如何在数据库中存储多组值？

Right now I'm creating a web-app which requires that multiple sets of data (including a URL, title, and date) be stored in a DB.

现在我正在创建一个Web应用程序，它要求将多组数据（包括URL，标题和日期）存储在DB中。

So for example, User1 may be storing Item 1 which consists of Example, http://example.com, and YY-MM-DD HH:MM:SS. And he may have multiple items with the same types of data.

因此，例如，User1可以存储由示例，http：//example.com和YY-MM-DD HH：MM：SS组成的项目1。并且他可能有多个具有相同类型数据的项目。

Before, I was sotring all the items in one field of my users table with something like:

以前，我在用户表的一个字段中输入所有项目，例如：

Example=>http://example.com=>YY-MM-DD HH:MM:SS[END]
Example2=>http://example2.com=>YY-MM-DD HH:MM:SS[END]
etc...

That would be combined to one big string, and then in PHP I'd separate each string by the separator [END] and then separate each of THOSE strings by the separator =>. Now I'm wondering if it would be better to store these items in their own table, so each user would have an 'Items' table (user1_items, user2_items, etc.). Is there a 'right' way to do this? Would using separate tables be overkill? Also, about how much more space, if any, would I be losing on my web-host? Just looking for some guidance on how to procede with this.

这将组合成一个大字符串，然后在PHP中我将每个字符串分隔符[END]分隔，然后通过separator =>分隔每个字符串。现在我想知道将这些项目存储在自己的表中是否更好，因此每个用户都有一个'Items'表（user1_items，user2_items等）。有没有'正确'的方法来做到这一点？使用单独的表是否过度杀伤？另外，关于我的网络主机上会丢失多少空间（如果有的话）？只是寻找一些如何处理这个问题的指导。

If any further elaboration is needed, please let me know!

如果需要进一步说明，请告诉我！

4 个解决方案

#1

Have a user table with information about each user along with a unique user id.

拥有一个用户表，其中包含有关每个用户的信息以及唯一的用户ID。

Have a data table where each piece of information (url, title, date) is a separate column. Add an additional column called user_id that indicates which user this piece of information belongs to.

有一个数据表，其中每条信息（网址，标题，日期）是一个单独的列。添加一个名为user_id的附加列，指示此信息所属的用户。

#2

I agree to go for separate tables.

我同意去单独的表格。

Also note that if you do decide to insert multiple values into a column, you don't need to insert your own separators like [END], just store it in an array and use serialize($array) to serialise it before storage, then $array = unserialize($dbResponse); when retrieving it from the database. This is assuming that the data is sanitized and safe.

另请注意，如果您决定在列中插入多个值，则不需要插入自己的分隔符，如[END]，只需将其存储在数组中并使用serialize（$ array）在存储之前将其序列化，然后$ array = unserialize（$ dbResponse）;从数据库中检索它时。这假设数据已经过消毒且安全。

#3

Putting your data in one long string would defeat the purpose of a database. It would be the equivalent of writing it to a text file. Databases are a way to organize, index, and relate data to each other.

将数据放在一个长字符串中会破坏数据库的用途。这相当于将其写入文本文件。数据库是一种相互组织，索引和关联数据的方法。

To visualize a database structure imagine a table like so.

为了可视化数据库结构，想象一下这样的表。

   id      name           url                         date
  ----    ------------   -----------------------     --------------------
    1      Example1       www.example1.com            10-07-14 13:56:13
    2      Example2       www.example2.com            10-07-15 00:06:37
    3      Example3       www.example3.com            10-07-16 16:20:00
    4      Example4       www.example4.com            10-07-17 09:18:22

each one is a row in the database and you can pull out a bunch of them based on a query and then do whatever you need with them.

每个都是数据库中的一行，您可以根据查询提取一堆，然后随意执行任何操作。

Also you could relate this data to the user who entered that data by adding a user_id column and inserting their user id in each record which would reference their user record in the users table.

此外，您可以通过添加user_id列并在每条记录中插入用户ID来将此数据与输入该数据的用户相关联，该记录将在users表中引用其用户记录。

#4

I would follow this form, because it turns out to be to me more rapid.

我会按照这种形式，因为事实证明对我来说更快。

With every unique user's index, it would create a consultation towards the table of articles connecting this unique user's index with a unique sub-index that would have the table with the information Example => http: // example.com => YY-MM-DD HH:MM:SS [END] Example2 => http: // example2.com => YY-MM-DD HH:MM:SS [END] etc...

对于每个唯一用户的索引，它将创建一个关于连接此唯一用户的索引与唯一子索引的文章表的咨询，该子索引将具有包含信息的表示例=> http：// example.com => YY-MM -DD HH：MM：SS [END]例2 => http：// example2.com => YY-MM-DD HH：MM：SS [END]等......

For example:

例如：

The user's only index, p.e. userid = '98'

用户唯一的索引，p.e。 userid = '98'

[User table]

    (Userid (Index))
    (Name)
    .
    .
    .

And


[Articles table]

 Subid(Articleid) 98
 (Url) http: // example.com
 (Datetime) YY-MM-DD HH:MM:SS

--------------------------

 Subid(Articleid) 99
 (Url) http: // example.com
 (Datetime) YY-MM-DD HH:MM:SS

__________________________

The same user's index connected to the same sub-index of articles. You were saving yourself consultations in the database, because with a consult you'll have all information.

相同用户的索引连接到相同的文章子索引。您在数据库中保存了自己的咨询，因为通过咨询您将获得所有信息。

#1