如何将数组存储在数据库中？ [英] How to store arrays in a database?

问题描述

我有一个简单的消息系统，直到现在使用基于文件的存储。因为系统的用户数量逐渐增加，我想切换到基于数据库的存储。$ p $ b在代码中，消息服务器维护一个用户列表（包括他们的凭据）和一个消息列表。 Message对象的主要字段有一个Sender（类型为User），收件人（类型为User []）和一个Content（类型为string）。$
通常，用户将请求邮件地址给他从服务器收到收件人字段包含自己的用户名的所有消息。

所以，对于数据库，我设想了以下表格：
-A Users表

-A消息表_
（ - 每个用户指定MessageID给他的消息的表）？

我遇到的问题是如何存储收件人字段（其中包含一个数组），使数据库查询可以搜索请求接收到他的消息的用户。我可以看到没有比为系统上注册的每个新用户动态创建一个表格更好的解决方案，该用户对其列为收件人的消息进行引用。其他方法可能吗？

非常感谢！

解决方案

blockquote>

我可以看到没有比
更好的解决方案为每个
新用户动态创建一个表...

您不应该看到关系数据库<动态创建的表 / a>观点。

---

一般来说，您的问题解决如下：

• 一个用户表与（user_id，name，surname ...）


• A 消息表与（message_id，time_stamp，subject，body，sent_by ...）


• A messages_recipients table（message_id，recipient_id）

sent_by 消息表中的c $ c>字段和 messages_recipients 中的 recipient_id code>表应该是外键到 user_id 用户表中的code>字段。

I have a simple messaging system that up till now uses file based storage. Because the number of users of the system is increasing gradually I would like to switch over to database based storage.

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

So, for the database I envision the following tables:
-A Users table
-A Messages table
(- a table for each user that specifies the messages addressed to him by MessageID)?

The problem I have is how to store the Recipients field (which contains an array) in such a way that a database query can search it for the user that requests to receive the messages addressed to him. I can see no better solution than to dynamically create a table for each new user that registers on the system that holds references to the messages in which he is listed as a recipient. Are other approaches possible?

Thanks a lot!

解决方案

I can see no better solution than to dynamically create a table for each new user...

You should never see "dynamically created tables" from a relational-databases point of view.

---

In general your problem is solved as follows:

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

Both the sent_by field in the messages table and the recipient_id field in the messages_recipients tables should be foreign keys to the user_id field in the users table.

这篇关于如何将数组存储在数据库中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！