MySQL table with multiple values ​​in one field

Question

I am creating a database for a real estate company. These properties are managed by sellers using the CodeIgniter website.

How do I start creating a database for this table. Fields, such as address / location, price, etc., are quite simple, but there are some sections, such as Kitchen Appliences, Property Usage, etc ... where sellers can check multiple values ​​for this field.

In addition, several people can be attached to the object (from people in the table), this can be a mediator, owner, seller, real estate lawyer, etc. .... Should I use one field for this or just create an additional table and normalize bindings?

Is it best to use only one field and use serialized data or is there a better way to do this?

Answer 1

In a relational database, you should NEVER EVER enter more than one value in one field — this violates the first normal form.

With sections such as Kitchen Appliances, Property Usage, etc., where sellers can check multiple values ​​for this field, it depends on whether it will always be the same set of several options that can be specified:

• If there is always the same set of several parameters, you can include a logical column for each parameter in the property table.
• If there are many different parameters that apply to different properties, it makes sense to create a separate table to store the available parameters and a link table to hold which parameters apply to which properties.

The first approach is simpler and can be expected to work faster, while the last approach is more flexible.

A similar consideration applies to people associated with each home; however, given that one property may have (for example) more than one owner, the second approach seems to be the only viable one. Therefore, I propose separate tables for storing information about a person (name, phone number, etc.) and the roles of people (for example, the owner, seller, etc.) and a link table for linking roles to properties and people.

Answer 2

You must create an additional table for it ....

For instance...

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

Answer 3

If you want your data to be in third normal form, you should consider adding additional tables, rather than encoding multiple values ​​in different fields. However, it depends on how far your belladonna goes.