创建一个表，但与复合键中的其他实体？(Creating a table but with other entities in a compound key?)

创建一个表，但与复合键中的其他实体？(Creating a table but with other entities in a compound key?)

我有2个主键表和1个组合表。

除了复合表中主键表中的2个主键以外，我还想获得其他信息。

我只是重复这些数据还是他们将其他字段添加到复合字段而不重复它的方式？

谢谢。

I have 2 Primary key tables and 1 Compound table.

I want to have some other information apart from the 2 primary keys from the primary key table in the compound table.

Would I just repeat this data or is their a way to add other fields into a the compound field without repeating it?

Thanks.

最满意答案

您目前的设计看起来接近正确。 但我认为你的EventVolunteer表应该如下所示：

CREATE TABLE EventVolunteer ( eventID INTEGER, volunteerID INTEGER, FOREIGN KEY eventID REFERENCES Event(eventID), FOREIGN KEY volunteerID REFERENCES Volunteer(volunteerID), PRIMARY_KEY(eventID, volunteerID) )

这张桥表应该存在以存储事件与其志愿者之间的关系，而不是别的。 活动和志愿者的所有元数据应分别在Event和Volunteer表中。

如果您需要提供一些信息，那么您可以通过使用此桥接表参加Event和Volunteer表。 如果你在正确的位置设置了指数，那么这种加入比你想像的要少得多。

Your current design looks close to right. But I think your EventVolunteer table should look like this:

CREATE TABLE EventVolunteer ( eventID INTEGER, volunteerID INTEGER, FOREIGN KEY eventID REFERENCES Event(eventID), FOREIGN KEY volunteerID REFERENCES Volunteer(volunteerID), PRIMARY_KEY(eventID, volunteerID) )

This bridge table should exist to store relationships between events and their volunteers, and nothing else. All metadata for events and volunteers should be in the Event and Volunteer tables, respectively.

If you need to bring in some information, then you can do so via joining the Event and Volunteer tables with this bridge table. This join is much less of a penalty than you might think, if you have indices setup in the right places.