VKK VKK - 1 year ago 39
MySQL Question

What is the best practice for designing an SQL table that relates to table A, table B, or table A and B?

Let's say I have three tables. I'm going to make these tables as simple as possible to illustrate the design question:

Users:

CREATE TABLE users (user_id INT PRIMARY KEY);


Networks:

CREATE TABLE networks (network_id INT PRIMARY KEY);


Network Users:

CREATE TABLE network_users (
network_id INT,
user_id INT,
PRIMARY KEY(network_id, user_id),
FOREIGN KEY(network_id) REFERENCES networks(network_id),
FOREIGN KEY(user_id) REFERENCES users(user_id)
);


As shown above users have a many to many relationship with networks as described in the Network Users table. This is all pretty conventional so far.

Now here's my question:

Let's say I want to add a messages table and I want to be able to store a message sent to:


  1. a specific user,

  2. a specific network,

  3. or to a user within a network



What's the best design approach for the messages table?

I could structure a messages table like this:

CREATE TABLE messages (
message_id INT PRIMARY KEY,
network_id INT,
user_id INT,
FOREIGN KEY(network_id) REFERENCES networks(network_id),
FOREIGN KEY(user_id) REFERENCES users(user_id)
);


where:


  1. messages sent to a specific user have the user_id field set and NULL for the network_id field

  2. messages sent to a specific network have the network_id field set and NULL for the user_id

  3. messages sent to a user within a network have both the network_id and user_id fields set



But is this the best approach? Since the network_id and user_id fields may be null, I have to create a separate primary key (message_id) as opposed to using a composite primary key (of network_id and user_id) as would otherwise be done.

Answer Source

What you describe in your question is known as a polymorphic association. Have a look at this article which describes some possible ways to model those.

While the article describe how to model a polymorphic association in the most general way possible, I think your approach is also good, because most probably won't have more than 3 message types (to a user, to a network and to a user in a network)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download