一个字段中有多个值的 MySQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9699534/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL table with multiple values in one field
提问by Simon
I'm building a database for a real estate company.. Those real estate properties are managed by the sales people using a CodeIgniter website.
我正在为一家房地产公司建立一个数据库。这些房地产由销售人员使用 CodeIgniter 网站进行管理。
How should I proceed with the database design for this table. The fields like address/location, price etc. are pretty straightforward but there are some sections like Kitchen Appliences, Property Usage etc... where the sales people can check multiple values for that field.
我应该如何继续这个表的数据库设计。地址/位置、价格等字段非常简单，但有一些部分，如厨房电器、物业使用等……销售人员可以在其中检查该字段的多个值。
Furthermore, a property can have multiple people attached to it (from the table people), this can be an intermediaire, owner, seller, property lawyer etc... Should I use one field for this or just I create an extra table and normalize the bindings?
此外，一个财产可以有多个人（来自表人），这可以是中间人、所有者、卖方、财产律师等......我应该为此使用一个字段还是只是创建一个额外的表并规范化绑定？
Is the best way to proceed just using one field and using serialized data or is there a better way for this?
仅使用一个字段并使用序列化数据的最佳方法是继续进行还是有更好的方法？
回答by
In a relational database you should never, EVER, put more than one value in a single field - this violates first normal form.
在关系数据库中，您永远不应该在单个字段中放置多个值 - 这违反了第一范式。
With the sections like Kitchen Appliances, Property Usage etc... where the sales people can check multiple values for that field, it depends on whether it will always be the same set of multiple options that can be specified:
对于厨房电器、物业使用等部分，销售人员可以检查该字段的多个值，这取决于它是否始终是可以指定的多个选项的相同集合：
- If there are always the same set of multiple options, you could include a boolean column on the property table for each of the options.
- If there are likely to be a variety of different options applicable to different properties, it makes more sense to set up a separate table to hold the available options, and a link table to hold which options apply to which properties.
- 如果总是有一组相同的多个选项，您可以在属性表中为每个选项包括一个布尔列。
- 如果可能有多种不同的选项适用于不同的属性，那么设置一个单独的表来保存可用选项，并设置一个链接表来保存哪些选项适用于哪些属性更有意义。
The first approach is simpler and can be expected to perform faster, while the latter approach is more flexible.
第一种方法更简单，可以预期执行得更快，而后一种方法更灵活。
A similar consideration applies to the people associated with each house; however, given that a single property can have (for example) more than one owner, the second approach seems like the only one viable. I therefore suggest separate tables to hold people details (name, phone number, etc) and people roles (such as owner, seller, etc.) and a link table to link roles to properties and people.
类似的考虑适用于与每个房屋相关的人；然而，考虑到单个财产可以拥有（例如）多个所有者，第二种方法似乎是唯一可行的方法。因此，我建议使用单独的表来保存人员详细信息（姓名、电话号码等）和人员角色（例如所有者、卖家等）以及将角色链接到属性和人员的链接表。
回答by
You should create extra table for it....
你应该为它创建额外的表......
For example...
例如...
Consider the scenario that 1 item may have many categories and 1 category may have many items...
Then you can create table like this...
Create three tables for that....
(1) tblItem :
fields:
itemId (PK)
itemName
etc..
(2) tblCategory :
fields:
categoryId (PK)
categoryName
etc..
(3) tblItemCategory :
fields:
itemId (PK/FK)
categoryId (PK/FK)
So according to your data you can create an extra table for it....
I think it would be optimize way....
回答by Philip Sheard
If you want your data to be in third normal form, then you should think in terms of adding extra tables, rather than encoding multiple values into various fields. However it depends how far your brief goes.
如果您希望您的数据采用第三范式，那么您应该考虑添加额外的表，而不是将多个值编码到各个字段中。但是，这取决于您的简介走多远。
