0

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.

VKK
  • 882
  • 7
  • 19
  • If user A in network N is different from user A in network O (send message to a specific user in a specific network), then users and networks are not in a many-to-many relationship, only in a one-to-many. – Shadow May 22 '17 at 20:12
  • Hm could you explain that further? A user can be in multiple networks and a network can contain multiple users. Isn't that a many to many relationship between networks and users? Message relationship #3 of a message to a user within a network would be a one to many relationship but thats a one to many relationship between messages and network_users; I would think that the messages-to-network_users relationship does not affect the users-to-networks relationship. – VKK May 22 '17 at 20:16
  • If the same user can be in multiple networks, then there is no point in sending a message to a specific user in a specific network. I just need to send the message to the user. – Shadow May 22 '17 at 20:43
  • The example above was really just meant to be illustrative. My actual scenario is far more complicated but suffice to say all three relationships have valid use cases. For example we could have a message that should only be accessible to a user while they are part of a particular network (case 3), or a message that is always accessible to a user regardless of network (case 1), or a message that is accessible to all the users who are in a given network at a particular moment (case 2). – VKK May 22 '17 at 20:59

3 Answers3

1

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)

Tudor Constantin
  • 26,330
  • 7
  • 49
  • 72
1

One approach I've seen is to have entity_id in messages along with entity_type where the type tells you whether the record refers to a user or a network (or some other type later). This response has some interesting examples of handling a case like this.

Carl
  • 1,253
  • 9
  • 23
0

If you have a many:many relationship among 3 things (A, B, C):

CREATE TABLE ABC (
    a_id ...,
    b_id ...,
    c_id ...,
    PRIMARY KEY(a_id, b_id, c_id),
    INDEX(b_id, c_id, a_id),
    INDEX(c_id, a_id, b_id)
) ENGINE=InnoDB;

The indexes let you get from any table toward the other tables.

But... Let's see some of your SELECTs before being sure that this is best.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Correct me if I'm wrong but don't all columns of a primary key have to be not null? If that is the case, how would I handle relationships #1 and #2 in my question? – VKK May 22 '17 at 21:26
  • Oops. Plan A: Use a specific (non-null) value instead of NULL. Plan B: My answer won't work. – Rick James May 22 '17 at 21:35