Database Modeling - Either/Or in Many-to-Many
- by EkoostikMartin
I have an either/or type of situation in a many-to-many relationship I'm trying to model.
So I have these tables:
Message
----
*MessageID
MessageText
Employee
----
*EmployeeID
EmployeeName
Team
----
*TeamID
TeamName
MessageTarget
----
MessageID
EmployeeID (nullable)
TeamID (nullable)
So, a Message can have either a list of Employees, or a list of Teams as a MessageTarget. Is the MessageTarget table I have above the best way to implement this relationship? What constraints can I place on the MessageTarget effectively? How should I create a primary key on MessageTarget table?