一个字段中有多个值的MySQL表 [英] MySQL table with multiple values in one field

问题描述

我正在为一家房地产公司建立一个数据库..这些房地产由使用CodeIgniter网站的销售人员管理。

我应该如何进行与此表的数据库设计。地址/位置，价格等字段很简单，但有一些部分，如厨房应用，属性使用等...销售人员可以检查该字段的多个值。

此外，一个属性可以有多个人附加到它（从表人），这可以是一个intermediaire，所有者，卖家，财产律师等...我应该使用一个字段为此或只是我创建一个额外的表并规范化绑定？

是使用一个字段并使用序列化数据的最好方法，还是有更好的方法？

解决方案

在关系数据库中，你永远不应该在一个字段中放置多个值 - 这违反了第一个正常形式。 >

对于厨房用具，属性使用等部分，销售人员可以检查该字段的多个值，这取决于它是否总是相同的多个集合可以指定的选项：

• 如果总是有多个选项的相同集合，则可以在属性表上包含一个布尔列


• 如果可能存在适用于不同属性的各种不同选项，则更有意义的是，设置一个单独的表来保存可用的选项，并且链接表以保存哪些选项适用于哪些属性。

第一种方法更简单，可以期望执行更快，方法更灵活。

类似的考虑适用于与每个房子相关的人;然而，鉴于单个财产可以拥有（例如）多个所有者，第二种方法似乎是唯一可行的。因此，我建议使用单独的表格来保存用户的详细信息（姓名，电话号码等）和人员角色（例如所有者，卖家等），以及用于将角色链接到媒体资源和人员的链接表。

I'm building a database for a real estate company.. Those real estate properties are managed by the sales people using a CodeIgniter website.

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?

解决方案

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.

这篇关于一个字段中有多个值的MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！