MySQL：在成员表列中存储结构化用户数据

时间：2021-11-10 16:58:50

I wanted to ask for some advice in structuring the SQL database I am creating.

我想在构建我正在创建的SQL数据库时请求一些建议。

UPDATE: Storing Data in MySQL as JSON seems to clearly indicate that storing JSON in MySQL is not a smart choice.

更新:在MySQL中存储数据作为JSON似乎清楚地表明在MySQL中存储JSON不是一个明智的选择。

My initial idea was to create a table for each user named '{user_id}' with the following rows:

我最初的想法是为每个名为“{user_id}”的用户创建一个包含以下行的表:

  1. Datetime
  2. Entry (one-digit int)

    3. 输入(一位数int)

  3. Comment (reasonably short string)

    4. 评论(合理的短字符串)

  4. Activity (one word)

    5. 活动(一个字)

However, I have read that creating a table for each user is not advisable because it's unmanageable in the long run.

但是,我已经知道为每个用户创建一个表是不可取的,因为从长远来看它是无法管理的。

Specifically, I wanted to know how I could put all the information that I would have put in the '{user_id}' table in the user's row of my 'members' table.

具体来说,我想知道如何将我放在“{user_id}”表中的所有信息放在用户的'成员'表的行中。

I had a few ideas, but don't know how good they are:

我有一些想法,但不知道它们有多好:

  • Storing the user data as a JSON object (converted to a string) in an additional column 'data' of the 'members' table. Would that become unmanageable in the long run too (due to JSON object string becoming too long)?

    将用户数据存储为“成员”表的附加列“数据”中的JSON对象(转换为字符串)。从长远来看,这会变得难以管理(由于JSON对象字符串变得太长)?

  • Storing the user data in various additional columns of the 'members' table, maybe one for each of the parameters listed above (each of them being an array)

    将用户数据存储在“成员”表的各个附加列中,可能对应于上面列出的每个参数(每个参数都是一个数组)

  • Storing the user data in various additional columns of the 'members' table, maybe one for each of the parameters listed above (each of them being a dictionary or some other data structure)

    将用户数据存储在“成员”表的各个附加列中,可能对应于上面列出的每个参数(每个参数都是字典或其他一些数据结构)

    •

Are there any other better ways, or better data storage types than JSON objects?

有没有比JSON对象更好的方法或更好的数据存储类型?

What would be a good way of storing this information? Isn't handling the arrays/dictionaries going to become unmanageable over time when they become very big?

存储这些信息的好方法是什么?当它们变得非常大时,不会随着时间的推移处理数组/字典变得无法管理吗?

(one thing to keep in mind is that the 'data' entries would have to be daily modified and easily accessed)

(要记住的一件事是'数据'条目必须每日修改并轻松访问)

1 个解决方案

#1

1  

I think you may simply want a single additional table, maybe called "activities" with a foreign key "user" to the "members" table. Then for each row in each of the per user table that you were originally thinking of, you have a row in the activities table with the value of "user" being the user in question. Since each row is of relatively small bounded size, one would expect the database to handle it well, and efficiency issues can be addressed by indexing. Basically I am agreeing with @MikeNakis

我想你可能只想要一个额外的表,可能被称为“活动”,外键“用户”到“成员”表。然后,对于您最初想到的每个用户表中的每一行,您在活动表中有一行,其中“user”的值是相关用户。由于每一行的有界大小相对较小,人们可以期望数据库能够很好地处理它,并且可以通过索引来解决效率问题。基本上我同意@MikeNakis

#1

1  

I think you may simply want a single additional table, maybe called "activities" with a foreign key "user" to the "members" table. Then for each row in each of the per user table that you were originally thinking of, you have a row in the activities table with the value of "user" being the user in question. Since each row is of relatively small bounded size, one would expect the database to handle it well, and efficiency issues can be addressed by indexing. Basically I am agreeing with @MikeNakis

我想你可能只想要一个额外的表,可能被称为“活动”,外键“用户”到“成员”表。然后,对于您最初想到的每个用户表中的每一行,您在活动表中有一行,其中“user”的值是相关用户。由于每一行的有界大小相对较小,人们可以期望数据库能够很好地处理它,并且可以通过索引来解决效率问题。基本上我同意@MikeNakis

标签：

相关文章