How to store arrays in a database?

Question

I have a simple messaging system that still uses file-based storage. As the number of users of the system is gradually increasing, I would like to switch to a database-based storage.

In the code, the message server maintains a list of users (with their credentials) and a list of messages. The Message object has as its main fields Sender (of type User), Recipients (of type User []) and Content (type strings).
Typically, a user requests messages addressed to him from the server and receives all messages in which the Recipients field contains his own username.

So, for the database, I present the following tables:
-A user
table -A message table
(- a table for each user that indicates the messages addressed to him by MessageID)

The problem is how to save the Recipients field (which contains the array) so that the database query can look for it for the user who is requesting messages addressed to him. I do not see a better solution than to dynamically create a table for each new user who is registered in the system containing links to messages in which he is indicated as the recipient. Are other approaches possible?

Thanks a lot!

Answer 1

, ...

" " .

---

, :

• A users (user_id, , ...)
• A messages (message_id, time_stamp, subject, body, sent_by...)
• A messages_recipients table (message_id, recipient_id)

sent_by messages recipient_id messages_recipients user_id users.

Answer 2

" " - - ().

, : msg , , "" . , - msgs + .

Answer 3

, , - . . , , . ?

Answer 4

You describe many-to-many relationships, so you need a connection table that is between the [Users] (recipients) and [Messages] tables.

For example, a table, [UserMessages], which contains its own primary key identifier, as well as two other columns representing user PK, and Message PK.

Queries, entities, etc. can then associate multiple messages with multiple users by combining these tables.