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?
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
Volunteer tables, respectively.
If you need to bring in some information, then you can do so via joining the
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.