2

I'm developing an Android application and JSON web services. Android application will consume those web services.

Also, I will have a database on both sides, and server side and client side will have the same Database schema.

I will explain what I'm trying to do:

  • Users create one EReport or more.
  • Every EReport has one QAP. QAP table will be a copy of server side database.
  • Every QAP has one or more Defect. Defect will be a copy of server side database.
  • A user can define defects if he or she needs it. To do it, I have added EReportDefect table.
  • A Defect could have one or more photos. I create EReportDefImg to store the relation between an EReport, and EReportDefect and one or more images.

My question is: Do I need eReportId as FK on EReportDefImg?

I've added that column because I could use to find all defect's images for an EReport.

enter image description here

VansFannel
  • 45,055
  • 107
  • 359
  • 626

3 Answers3

1

The human logic says if there is a reportdefect there must be a report. so use the FK. I think this will maybe help you later.

irokee
  • 80
  • 4
1

Do I need eReportId as FK on EReportDefImg?

No.

The EReportDefImg.eReportId would allow an EReportDefImg row to reference a EReportDefect row such that EReportDefect.eReportId is different from EReportDefImg.eReportId.

I've added that column because I could use to find all defect's images for an EReport.

You can still do that (without EReportDefImg.eReportId) by JOINing EReportDefImg and EReportDefect to get the EReportDefect.eReportId.

If you want to avoid the JOIN, you can use the identifying relationship to include the eReportId in the EReportDefect's PK, which would then also migrate it to EReportDefImg.eReportId (which you could then use directly for filtering, without JOIN). Ditto for defectId.

enter image description here

NOTE: I added eReportDefectNo to allow multiple EReportDefects for the same combination of EReport and Defect. If that's not needed, remove the eReportDefectNo.

BTW your model, as it is now, allows for a single EReportDefect to be connected to two different QAPs (one through EReport an the other through Defect). Is that intentional?

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • With two different QAP? No, it isn't intentional. A defect can only belong to a QAP. – VansFannel Sep 14 '12 at 10:47
  • @VansFannel Then you'd need to use identifying relationships in higher levels of your model as well, like explained [here](http://stackoverflow.com/a/12350763/533120). – Branko Dimitrijevic Sep 14 '12 at 10:54
  • I've read what you've suggested me but I don't understand anything (maybe because of my English level). How can I avoid that "problem" on my design? Thanks. – VansFannel Sep 14 '12 at 12:11
  • 1
    @VansFannel Well, try to imagine what would happen if you try to insert at the bottom of the diamond. Without identifying relationships, sides of the diamond could point to whatever they like, including different tops. With identifying relationships, the top PK is propagated to the very bottom, so both sides must match it (lest side-to-bottom FK is violated), so they cannot differ in their choice of the top. – Branko Dimitrijevic Sep 14 '12 at 12:32
  • Ok, so I need to propagate qapId to every table, isn't it? – VansFannel Sep 14 '12 at 12:43
0

No, you don't, this kind of design is performed usually for performance reasons - omiting unnecessary joins. If you don't have such a problem, than don't do this.

By the way, you seem to put too many foreign keys into primary keys. This is done to help maintain data consistency, but usually it is not a good practise due to performance degradation.

Btw. what is the defectId foreign key in EReportDefect for?

WojtusJ
  • 1,318
  • 1
  • 12
  • 19
  • `EReportDefect.defectId` id a FK to `Defect` table. It will contain null because `EReportDefect` can contain user's defined `Detect`. This question is related http://stackoverflow.com/questions/12425606/two-tables-almost-identical. – VansFannel Sep 14 '12 at 15:13