一列作为主键或两个外键作为主键 [英] A column as primary key or two foreign keys as primary key

问题描述

我有以下数据库设计:

一份电子报告有一个QAP，其中有一些Requirement.一个QAP 及其Requirement 可以用于多个E-Report.

每个Requirement 在每个电子报告中都会有一个是/否确认.我添加了 EReportReq 来存储需求确认值(用户将设置这些值).

而且，每个Requirement在每个E-Report上都会有多个Image.EReportReqImg 将存储 Image 和 Requirement 关系.

如果您需要有关此数据库模型的更多详细信息，请告诉我.

我的问题是关于 EReportReq 表.我不确定我是否需要一列作为主键 (EReportReqId) 或者我可以使用 eReportId 和 requirementId 作为主键.>

如果我使用这两列，eReportId 和 requirementId 作为主键，我需要将这两列添加到 EReportReqImg 表，所以我不知道这个方法是否比我的好.

你怎么看?

解决方案

我的问题是关于 EReportReq 表.我不确定我是否需要一列作为主键 (EReportReqId) 或者我可以使用 eReportId 和 requirementId 作为主键.>

您可以使用它们中的任何一个 - 它们都不是绝对更好"的.请注意，如果您决定使用第一种方法，还要在 {eReportId, requirementsId} 上创建一个 UNIQUE 约束.

第一种方法(具有非识别关系和代理键)导致:

• 子表中的精简"外键(在本例中为 EReportReqImg) - 正如您已经注意到的，
• 级联 ON UPDATE 不会传播到子级(因此，如果您更新 EReport.eReportId，则只有 EReportReq.eReportId 是级联更新的，而不是 EReportReqImg.eReportId)
• 并且可以对 ORM 更加友好.

另一方面，第二种方法(使用识别关系和自然键):

• 对 JOIN 的需求可能较少(例如，您不需要 EReportReqImg JOIN EReportReq 只是为了找出 requirementId - 您直接在 EReportReqImg 中拥有它.requirementId),
• 更适合集群表(例如，具有相同 eReportId 的 EReportReq 行将在物理上关闭"存储，这可能会显着有益于某些查询)
• 避免在代理键上附加索引.

由于您有少量子表，胖"FK 无关紧要，而且由于我们正在处理 ID，因此它们不太可能更改，并且级联 ON UPDATE 不太可能成为问题.因此，我的直觉是采用第二种方法，但您可能还有其他一些考虑因素，可能会使您的决定转向不同的方向......

I have the following database design:

An E-Report has one QAP which has some Requirements. A QAP and its Requirements can be used in more than one E-Report.

Every Requirement will have a Yes/No confirmation in each E-Report. I've added EReportReq to store requirements confirmations values (users will set these values).

And also, each Requirement will have more than one Image on each E-Report. EReportReqImg will store Image and Requirement relationship.

If you need more details about this database model, please tell me.

My question is about EReportReq table. I'm not sure if I need a column as primary key (EReportReqId) or I can use eReportId and requirementId as primary key.

If I use these two columns, eReportId and requirementId as primary key, I will need to add these two to EReportReqImg table, so I don't know if this approach is better than mine.

What do you think?

解决方案

My question is about EReportReq table. I'm not sure if I need a column as primary key (EReportReqId) or I can use eReportId and requirementId as primary key.

You can use either of them - none of them is absolutely "better". Just be careful that if you decide to use the first approach, also create a UNIQUE constraint on {eReportId, requirementId}.

The fist approach (with non-identifying relationship and surrogate key) leads to:

• "leaner" foreign keys in child tables (which is EReportReqImg in this case) - as you already noted,
• the cascading ON UPDATE doesn't propagate to children (so if you update EReport.eReportId, only EReportReq.eReportId is cascade-updated, but not EReportReqImg.eReportId)
• and can be more friendly to ORMs.

On the other hand, the second approach (with identifying relationship and natural keys):

• has potentially less need for JOINs (e.g. you don't need to EReportReqImg JOIN EReportReq just to find-out requirementId - you have it directly in EReportReqImg.requirementId),
• is better suited for clustered tables (e.g. EReportReq rows with the same eReportId will be stored physically "close", which may significantly benefit some queries)
• avoids additional index on the surrogate key.

Since you have a small number of child tables, "fat" FKs don't matter much and since we are dealing with IDs, they are unlikely to change and cascading ON UPDATE is unlikely to be a problem. So, my instinct is to go with the second approach, but you might have some other considerations that might tip your decision in a different direction...

这篇关于一列作为主键或两个外键作为主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！