lazerk lazerk - 9 months ago 23
SQL Question

Creating a table but with other entities in a compound key?

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.

Answer Source

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.