CODEWITHSUNDEEP
sqldatabase
Abhi.Net
Abhi.Net

Reputation: 772

Multiple Foreign keys between two tables

I have a table called Tbl_Event that stores basic information for upcoming events.

Tbl_Event

EventID  Description  Location

I have another table called staff that stores staff information

  Tbl_Staff

 StaffID  Name

Now each event can have multiple staff members, to achieve this I have created a new table

   tbl_Event_Staff

   RecordID  StaffID   EventID

StaffID and EventID have a COMBINED unique constraint on them. There is an added condition, where one of the staff member needs to be the supervisor of the event. What is the best possible solution to achieve this, Should I add an extra column in tbl_event - SupervisorID

Tbl_Event

EventID  Description  Location  SupervisorID

and link it to tbl_Event_Staff on StaffID( in this case there will be to foreign keys between these two tables - EventID and StaffID)

or * I dont think this is the best solution to to redundant data.

Should I add an extra column to tbl_Event_Staff - isSupervisor(bool) and for each row have a boolean variable that denotes if the staff for that EVENT is supervisor or not.

 tbl_Event_Staff

   RecordID  StaffID   EventID   IsSupervisor
      1        10        3         true
      2        20        3         false
      3        30        3         false

or

Is there an alternative solution?

Upvotes: 0

Views: 1308

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

I would use the second approach, and, however possible, would place a constraint on this table such that only one row for each EventID can be a supervisor.

The details of how to do this vary between databases - in some systems, it may have to be enforced with a trigger. In SQL Server, it could be done using a filtered index:

CREATE UNIQUE INDEX ON EventStaff (EventID) WHERE IsSupervisor = 1

I'd also recommend doing away with the tbl_ prefixes - there's no sane reason to use prefixes to distinguish object types in SQL - the syntax of the language means that you can tell the type of an object purely by its position in the query - except in one situation.

The situation I refer to is that views and tables may appear in the same positions in queries and be indistinguishable. However, this is a good thing - in general, you shouldn't care whether the object you're dealing with is a table or a view - the database developer should be free to create a different table structure and re-create the older structure using views in the place of the original tables - and existing queries shouldn't be affected.

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727077

The first solution is better, because it automatically guards you from creating events with zero supervisors and/or events with multiple supervisors: when an event's supervisor_id column is non-nullable, your RDBMS ensures that there is precisely one supervisor for each event.

If you need to get your results in the form of a table with a true/false indicator marking the supervisor, you can always get that result by joining to Tbl_Event, and comparing Tbl_Event.supervisor to tbl_Event_Staff.StaffID.

Upvotes: 1

Related Questions