A new column as primary key or use two FK as PK

Question

I have the following database design:

And I'm wondering if EReportReq must have a column as a primary key, eReportReqId, or maybe I can use eReportId and requirementId as primary key.

Requirement table will have system information and EReport table (which is not shown here), EReportReq and EReportReqImg will have user data.

EReportReq represents requirement which are part of an EReport. A requirement could be part of more than one EReport.

And EReportReqImg represents images (one or more) for those requirement that are part of an EReport.

Do I need eReportReqId as primary key?

Answer 1

Assuming an EReport cannot be connected to the same Requirement more than once, {eReportId, requirementId} is a key in any case. The only question is: do you need an additional surrogate key (eReportReqId)? Here are some criteria that might help you decide.

My personal hunch is that the answer to that question is "no", so you'll end-up with a model like this:

This model allows for less JOINing in some cases. For example, getting all images of given EReport can be sattisfied by just scanning the EReportReqImg - the eReportId is already there so we can use it directly to filter data (instead of having to JOIN with EReportReq just to get the eReportId).

It also nicely clusters the data in indexes (and potentially whole tables if your DBMS supports it) making certain range scans very efficient (including the one mentioned above).

Answer 2

You don't need eReportReqId as a primary key for your table, though in some cases you would want it to have it's own primary key.

Answer 3

It depends on the FKs in EReportReq. If the combination of the FKs eReportId and requirementId must be unique in EReportReq, then of course you could generalize it and use the FKs as the primary key of EReportReq. Otherwise, you will need a separate column (i.e. eReportReqId) for the primary key.

As a side note, if you were going to generalize it and use the FKs as the primary key, maybe you should consider normalizing the EReportReqImg table into EReportReq too.