3

I have following two tables in an MS SQL-Server Database:

user_info
  ( user_id PRIMARY KEY, 
    email, 
    password
  ) 

and

messages
  ( time_stamp, 
    message, 
    sender, 
    recipients, 
    FOREIGN KEY(sender) 
      REFERENCES user_info (user_id), 
    FOREIGN KEY(recipients) 
      REFERENCES user_info (user_id)
  ) 

I am wondering if it is possible to store multiple user_id values in the recipients field of messages table. I am a newbie, please help me how to solve this problem.

1 Answer 1

8

The best way to do this is with a one-to-many relationship message_recipients table.

user_info
( 
  user_id INTEGER PRIMARY KEY, 
  email VARCHAR(256), 
  password VARCHAR(256)
);

messages
( 
  message_id INTEGER PRIMARY KEY
  time_stamp DATETIME, 
  message VARCHAR(4000), 
  sender INTEGER -- references user_info.user_id
);

message_recipients
(
  message_id INTEGER, -- references messages.message_id
  recipient_id INTEGER -- references user_info.user_id
);
2
  • 1
    what if I make message_id and recipient_id as composite primary key? Sep 19, 2016 at 10:16
  • 1
    @MAKhan do you mean on the message_recipients table? As long as the same recipient can't be on the same message twice that would be a good constraint.
    – Brian J
    Sep 19, 2016 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.