两张桌子几乎相同 [英] Two tables almost identical

问题描述

我正在开发Android应用程序和JSON Web服务。双方将拥有一个数据库，两者都将具有相同的数据库模式。

在客户端，订单，QAP和缺陷是从服务器端数据库的副本。我不能在这些表中添加更多的行。

用户将有一个缺陷名称和四列的表单： CRS ， CRF ， MA 和 MI 。在这四列上，用户将插入一些值。缺陷名称为 Defect.description ，CRS，CRF，MA和MI值将作为当前电子报告的一部分。

要存储这些值（CRS，CRF，MA和MI），我使用 eReportDefect 。

我的问题是用户可以添加更多的缺陷。如果我可以在缺陷表中添加更多行，我将不会有问题，但我不能。为了解决这个问题，我添加了 UserDefect 表，但是我不知道这是否是一个很好的方法，因为我有两个表， eReportDefect 和 UserDefect ，几乎相同。

而对于填写在EReport中的每个缺陷，我都有另一个问题，我可以有零个，一个或多个图像（表 eReportDefImg 和 UserDefImg ）。

这个设计是OK还是可以改进？

解决方案

我可以在主键定义中看到一些老派的做法。 ;-)关于表 eReportDefect 您的密钥是多余的，因为据了解， defectId 已经是唯一的，所以 qapId 和 eReportId 只会导致索引更大，因此更慢 - 情况不是最好的。在缺陷和缺陷表中是正确的，而同样的错误在 eReportDefImg 。

好的，回到这一点，如果你不能修改 eReportDefect 没有别的办法像创建另一个表。

另一件事，图像。如果我理解正确，您可以修改表 eReportDefImg ，所以不需要 UserDefImg 表，甚至对于图像表。而不是这三个表，您可以使用一个缺陷表：

  PK image_id 
 FK userDefectId 
 FK defectId 
  

如果 userDefectId IS NULL，然后 defectId IS NOT NULL并指向 eReportDefect ，另一个四处走动这将通过 UserDefectImg 和 eReportDefImg 表来节省您的存储和耗时的连接。如果一个图像可以适应多个缺陷，那么实际上就是需要的。

I'm developing an Android application and JSON web services. Both sides will have a database, and both of them will have the same database schema.

On client side, Order, QAP and Defect are a copy from server side database. I can't add more rows to those tables.

User will have a form with defect name and four columns: CRS, CRF, MA and MI. On these four columns user will insert some values. Defect's name is Defect.description, and CRS, CRF, MA and MI values will be part of current E-Report.

To store these values (CRS, CRF, MA and MI) I use eReportDefect.

My problem is that user can add more defects. If I could add more rows on Defect table, I won't have a problem, but I can't. To solve it I have added UserDefect table but I'm not sure if this is a good approach because I have two tables, eReportDefect and UserDefect, almost identical.

And I have "another problem" for every Defect filled in an EReport, I could have zero, one or more images (tables eReportDefImg and UserDefImg).

Is this design "OK" or it could be improved?

解决方案

I can see some "old school" approach in primary key definition. ;-) Regarding the table eReportDefect your key is redundant, because as I understand "defectId" is already unique, so "qapId" and "eReportId" will only cause an index to be bigger thus slower - also the column positions in that case aren't the best. It is correct within Defect and DefectImg tables, while the same mistake is within eReportDefImg.

Ok, back to the point, if you can't modify eReportDefect table there is no other way like create another table.

Another thing, images. If I understand correctly you are able to modify table eReportDefImg, so there is no need for UserDefImg table and even for Image table. Instead of those three tables you can use one DefectImg table having:

PK image_id
FK userDefectId
FK defectId

If userDefectId IS NULL, then defectId IS NOT NULL and points to eReportDefect, and the other way around. This will save you storage and time consuming joins through UserDefectImg and eReportDefImg table. Those would be actually needed if one image could fit into more than one defects.

这篇关于两张桌子几乎相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！