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

问题描述

我有以下数据库设计：

一个电子报告有一个 QAP 其中有一些要求 s。 QAP 及其要求可用于多个电子报告。

每个要求将在每个电子报告中都有是/否确认。我添加了 EReportReq 以存储需求确认值（用户将设置这些值）。

而且，每个要求将在每个电子报表上拥有多个图像 。 EReportReqImg 将存储图像和需求关系。

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

我的问题是关于 EReportReq 表。我不知道我是否需要一列作为主键（ EReportReqId ），或者我可以使用 eReportId 和 requirementsId 作为主键。

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

你觉得怎么样？

解决方案>

我的问题是关于 EReportReq 表。我不知道我是否需要一列作为主键（ EReportReqId ），或者我可以使用 eReportId 和 demandId 作为主键。

您可以使用其中任何一个 - 它们中没有一个绝对更好请注意，如果您决定使用第一种方法，还可以在 {eReportId，requirementsId} 上创建一个UNIQUE约束。

第一种方法（具有非识别关系和替代键）导致：

• 子表中的瘦外键在这种情况下，这是 EReportReqImg ） - 正如您已经指出的那样，


• 级联ON UPDATE不会传播给子级（所以如果您更新 EReport.eReportId ，只有 EReportReq.eReportId 是级联更新，但不是 EReportReqImg .eReportId ）


• ，可以更加友善的ORM。

<另一方面，第二种方法（具有识别关系和自然键）：

• 可能不太需要JOIN（例如你不需要 EReportReqImg JOIN EReportReq 只是为了找出 requirementsId - 你直接在 EReportReqImg.requirementId ），


• 更适合聚集表（例如 EReportReq 具有相同 eReportId 的行将被物理关闭存储，这可能会显着有益于一些查询）


• 避免代理键上的附加索引。

由于您有少量子表，胖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屋！