CODEWITHSUNDEEP
sqlsql-server-2008database-design
EkoostikMartin
EkoostikMartin

Reputation: 6911

Database Modeling - Either/Or in Many-to-Many

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?

Database in question is SQL Server 2008

Upvotes: 1

Views: 862

Answers (6)

Cratylus
Cratylus

Reputation: 54084

The way you present this, it seems that an Employee is-a MessageTarget and also the Team is-a MessageTarget.
So a Message has a Target which is either an Employee or a Team.
Seems to me this is like inheritence (or composition) problem in SQL.
Check this out "Implementing Table Inheritance in SQL Server" . An Employee is not a MessageTarget per se but perhaps the readings on this can help you on your modelling

Upvotes: 3

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

Since employees and teams cannot mix in the same message, you'll need to do something like this:

enter image description here

  • MessageEmployee.MessageIdForEmployee references Message.MessageIdForEmployee.
  • MessageTeam.MessageIdForTeam references Message.MessageIdForTeam.

And there is the following constraint on the Message table:

CHECK (
    (MessageIdForEmployee = MessageId AND MessageIdForTeam IS NULL)
    OR
    (MessageIdForEmployee IS NULL AND MessageIdForTeam = MessageId)
)

Note how we have a separate junction table for each kind of child table, and junction tables don't reference parent's PK. Instead each junction table references a separate UNIQUE field. Since only one of these fields can be non-NULL, only one kind of child items can be connected to any given message.

NOTE: It is not strictly necessary to match MessageId with MessageIdForEmployee or MessageIdForTeam, but it may simplify querying somewhat.

Upvotes: 2

Jens Schauder
Jens Schauder

Reputation: 81950

So you want to ensure that MessageTargets for a single message all have the employeeID set or the teamID, but not a mixture of both?

Depending on your RDBMS you might be able to create Materialized view and put a constraint on that. The view would look like

select messageId, count(employeeId), count(teamId) from messageTarget

On that you would place a check constraint ensuring that one of the counts is zero.

Alternatively you could replace the MessageTarget with two tables: EmployeeMessageTarget and TeamMessageTarget, each only containing a TargetId and either an EmployeeId in the first table, and a TeamId in the second table.

Your Message table would get two new fields: an EmployeeMessageTargetId and a TeamMessageTargetId plus a check constraint ensuring at least one of those is null. If you make both fields unique you can have a foreign key from the *MessageTarget tables.

Upvotes: 3

kolossus
kolossus

Reputation: 20691

Consider removing the the "Team ID" field in the MessageTarget entity so that you only have messageId and employeeId. To cater for the team thing (if it is a requirement that each employee must be a member of a team), you can have another entity "Team Messages" where a database trigger will ensure that on insert into that table, you can insert a row in the message target table for each employee in the team. This way you can conveniently link back from each message to an employee from the MessageTarget table or back to a team from the "Team Messages" table. Also gonna make for convenient access in an ORM framework where the Employee entity simply has a List and the Team also has the same

Upvotes: 0

Walter Mitty
Walter Mitty

Reputation: 18940

is-a relationships are often instances of the gen-spec pattern. Class Table Inheritance is one way to design tables for cases of gen-spec.

http://martinfowler.com/eaaCatalog/classTableInheritance.html

Upvotes: 1

Randy
Randy

Reputation: 16677

you may also consider

MessageTarget
----
MessageID
targetID (not nullable)
targetType

then set the type to whichever it should be...

Upvotes: 1

Related Questions