如何存储多值个人资料详细信息？
我有许多多值字段，但不知道如何存储它们？如果我做 3NF 那么就会有很多表。例如：国籍。
一个人可以有单一或双重国籍。如果是双的，则意味着它是一对多。所以我创建一个用户表和一个 user_nationality 表。 （已经有国籍查找表）。或者我可以将两个国籍放入同一行，例如“美国，德国”，然后在运行时将其反序列化。但我不知道我是否可以搜索到这个？就像如果我只搜索德国人会出现吗？
这是一个例子，我有超过 30 个多值字段，所以我假设我不会为此创建 61 个表？ 1 个用户表、30 个查找表来保存每个多值项目的查找，以及 30 个表来保存多值项目的 user_ 值？
您还必须记住，一些多值字段组合在一起，例如“我曾就读的大学”，它有一组字段，例如大学名称、学位类型、时间线等。并且一个用户可以拥有 1 到多个这些。因此，我假设我可以为此创建一个单独的表，例如带有这些字段的 user_education ，但是假设其中一个字段也是固定的多值列表，就像我访问过的大学校园一样，那么我们最终将陷入一个永无休止的 FK 表链中对于社交网络来说，这并不是一个好的设计，因为其目标是将尽可能多的数据放入尽可能少的表中以提高性能。
I have many fields which are multi valued and not sure how to store them? if i do 3NF then there are many tables. For example: Nationality.
A person can have single or dual nationality. if dual this means it is a 1 to many. So i create a user table and a user_nationality table. (there is already a nationality lookup table). or i could put both nationalities into the same row like "American, German" then unserialize it on run-time. But then i dont know if i can search this? like if i search for only German people will it show up?
This is an example, i have over 30 fields which are multi-valued, so i assume i will not be creating 61 tables for this? 1 user table, 30 lookup tables to hold each multi-valued item's lookups and 30 tables to hold the user_ values for the multi valued items?
You must also keep in mind that some multi-valued fields group together like "colleges i have studied at" it has a group of fields such as college name, degree type, time line, etc. And a user can have 1 to many of these. So i assume i can create a separate table for this like user_education with these fields, but lets assume one of these fields is also fixed list multi-valued like college campuses i visited then we will end up in a never ending chain of FK tables which isn't a good design for social networks as the goal is it put as much data into as fewer tables as possible for performance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
你说得对，61 是表的最大数量，但实际上它可能会更少，举个你自己的例子：
在这种情况下，您可能只有一张“拼贴”桌子，因此此布局中将有四张桌子，而不是五张。
我想说的是，如果您要建模的数据集很大，不要害怕使用大量表 - 只需确保您保持最新的 ERD，这样您就不会迷失方向！另外，不要过多地陷入“链接表”范式 - “链接表”本身可以是“实体”，例如，您可以将“我曾就读的大学”链接表视为一个相反，“拼贴注册”表，为其提供自己的主键，并将每次支付课程费用的时间存储为（链接的）“拼贴注册付款”表中的行。
You're right that 61 is the maximum number of tables, but in reality it'll likely be less, take your own example:
In this case you'll probably only have one "collage" table, so there would be four tables in this layout, not five.
I'd say don't be afraid of using lots of tables if the data set you're modelling is large - just make sure you keep an up to date ERD so you don't get lost! Also, don't get caught up too much in the "link table" paradigm - "link tables" can be "entities" in their own rights, for example you could think of the "colleges i have studied at" link table as an "collage enrolments" table instead, give it it's own primary key, and store each of the times you pay your course fees as rows in a (linked) "collage enrolment payments" table.
您可能还想查看 ORM，它将自动将您的对象映射到数据库。
http://en.wikipedia.org/wiki/List_of_object-relational_mapping_software#PHP
You might also want to look at ORM which will map your objects to a database automatically.
http://en.wikipedia.org/wiki/List_of_object-relational_mapping_software#PHP
简单的解决方案是停止使用 SQL 表。这就是 NoSQL 的设计目的。查看 CouchDB 或 Mongo。每个值都可以存储为完整的结构 - 因此整个问题可以简化为单个（不是真正的）表。
几乎所有基于 SQL 的解决方案的缺点都是速度很慢。获取单个用户时速度慢 - 大量 JOIN 语句不会快速执行，或者搜索时速度慢（如果您决定将这些值存储为序列化）。
The simple solution is to stop using a SQL table. This what NoSQL is deigned for. Check out CouchDB or Mongo. There each value can be stored as a full structure - so this whole problem could be reduced to a single (not-really-)table.
The downside of pretty much any SQL based solution is that it will be slow. Either slow when fetching a single user - a massive JOIN statement won't execute quickly or slow when searching (if you decide to store these values as serialized).
如果您的用户表有很多相关属性，那么一种可能性是创建一个包含诸如（user_id、attribute_name、attribute_value）之类的行的属性表。您可以将所有属性存储到一张表中。您可以使用此表获取给定用户的属性，也可以按属性名称和值进行搜索。
If your user table has a lot of related attributes, then one possibility is to create one attributes table with rows like (user_id, attribute_name, attribute_value). You can store all your attributes to one table. You can use this table to fetch attributes for given users, also search by attribute names and values.
如果您需要继续使用 SQL，则需要创建这些表。您需要决定您愿意走多远，并对系统施加限制（例如只能指定一个校区）。
就国籍而言，如果您只需要两个国籍（最坏的情况），您可以考虑使用第二个国籍字段（Nationality 和 Nationality2）来解决这一问题。当然，这仅适用于不同值的最大数量较少的字段。
If you need to keep using SQL, you will need to create these tables. you will need to decide on how far you are willing to go, and impose limitations on the system (such as only being able to specify one campus).
As far as nationality goes, if you will only require two nationalities (worst-case scenario), you could consider a second nationality field (Nationality and Nationality2) to account for this. Of course this only applies to fields with a small maximum number of different values.