MySQL table with multiple values in one field

Question

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?

score 7 · Accepted Answer · answered Mar 14 '12 at 10:18

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.

score 3 · Answer 2 · answered Mar 14 '12 at 10:00

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....

score 1 · Answer 3 · answered Mar 14 '12 at 10:00

1

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.

answered Mar 14 '12 at 10:00

Philip Sheard

5,789
5
27
42

Thank you for your feedback. But from an MVC point of view, should I create a Class/Controller for every table then? – Simon Mar 14 '12 at 10:16
That goes beyond the scope of database design, and is a different question altogether. – Philip Sheard Mar 14 '12 at 10:21
From an MVC point of view Controllers do not have anything to do with database tables. – nem75 Mar 14 '12 at 16:09
2

@nem75: Simon is using CodeIgniter. I guess there is a convention to have model+view+controller for every database table. – bancer Mar 15 '12 at 01:02
1

@bancer: From the CodeIgniter user guide: "CodeIgniter has a fairly loose approach to MVC since Models are not required. If you don't need the added separation, or find that maintaining models requires more complexity than you want, you can ignore them and build your application minimally using Controllers and Views. CodeIgniter also enables you to incorporate your own existing scripts, or even develop core libraries for the system, enabling you to work in a way that makes the most sense to you." Anyway, I don't agree with their take on MVC, but that's another topic. :) – nem75 Mar 16 '12 at 11:29

MySQL table with multiple values in one field

3 Answers3

Linked