CODEWITHSUNDEEP
mysql
Nader Besada
Nader Besada

Reputation: 389

MYSQL Selection Value Table Structure

I am creating a website where users can post a listing of their home. I have checkboxes where users can check the characteristics their home contains such as a pool, fireplace, attached/detached garage etc.

I had to designs in mind but I was wondering which is more correct:

  1. Create a column in the home listing table for each characteristics and give it a type of enum('0','1') where 0 stands for not checked and 1 stands for checked

  2. Create a table which holds all the characteristics a property can have like: garage, pool, fireplace etc.. and then create a second table that pulls the characteristic id and pairs it with a home listing id

For eg: home_1 has a pool so a row will be created like this:

| home_1 | 1 |

where home_1 is the listing id and 1 is the id of pool in the characteristics table

Which option should I go with?

Upvotes: 0

Views: 35

Answers (3)

Karan Desai
Karan Desai

Reputation: 3142

It all depends on your method of using the data after you save them. But the basic idea should be to consider mappings in these ways:

  1. Go with the second option when:

If the two entities are many-many (many homes, many characteristics) you should go with the second option (even if it adds little cost of using joins in future).

Since your full db mapping is not known, I am proposing one more option IF the characteristics are independent of property. Meaning, if you are planning to use characteristics to reference some other entities of other tables, then it will be best again to go with your second option.

  1. Go with the first option when

If it is just one-many relationship (one home, many characteristics), your first option works good because not only it would reduce cost while fetching but also will update/remove the dependent characteristics of home when your home record gets updated/deleted.

Lastly, Its only up to you to decide the mapping type and dependencies of data models.

Upvotes: 0

user2102266
user2102266

Reputation: 539

If you want to query the data like "count all detached houses" Enum with seperate columns will work faster and easier to handle db operations.

If you are willing to query houses ONLY ON addresses, price and such NOT those features. 2nd method is easier to develop and maintain.

In short, use 2nd method if u are not going to query those house characteristics individually.

Upvotes: 0

Humble Freak
Humble Freak

Reputation: 459

Option 1 seems good, because if you go with 2nd option then there will be joins while querying the database. And join are expensive and time taking in MySQL.

more can be found here https://www.percona.com/blog/2013/07/19/what-kind-of-queries-are-bad-for-mysql/

Upvotes: 1

Related Questions