HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to store multiple foreign key values in a single field?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
fieldforeignstoresinglemultiplehowvalueskey

Problem

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.

Solution

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
);

Code Snippets

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
);

Context

StackExchange Database Administrators Q#150023, answer score: 8

Revisions (0)

No revisions yet.