snippetsqlMinor
How to store multiple foreign key values in a single field?
Viewed 0 times
fieldforeignstoresinglemultiplehowvalueskey
Problem
I have following two tables in an MS SQL-Server Database:
and
I am wondering if it is possible to store multiple
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.