storing multiple values in column db design approach

Question

I need have a table with columns -> hotel_id, hotel_name, price_per_night, facilities, preferred

In this columns facilities and preferred may have multiple values.

For example->

facilities can be swimming pool, spa etc.(many diff values can be added with time)

preferred can be couples, family etc.(many diff values can be added with time)

I am pretty new to mysql. I am confused how to design my database and thought of following approaches ->

To have a multiple values in a single column (which is a bad idea)
To create three tables ->

a.first would be hotel_main with columns hotel_id, hotel_name, price_per_night

b.second would be facilities_hotel with hotel_id, hotel_name, facility

c.third would be preferred_hotel with hotel_id, hotel_name, preferred

I would like to have more suggestions on how to design my database.

Option 1 violates 1NF, which as you say is a bad idea. Go with option 2, but don't duplicate hotel_name in each table (which is a violation of 2NF)—just keep it in the hotel_main table alone. Read up on database normalisation more generally. — eggyal
– eggyal, Commented Mar 12, 2016 at 8:42

Chris Travers · Accepted Answer · 2016-03-12 15:02:59Z

1

In general non-1NF designs are bad ideas in transactional processing systems. You run into lots of update/delete integrity problems which are hard to solve and MySQL doesn't give you even the tools to make inserts safe.

Where people use non-1NF designs effectively these usually meet two criteria:

They are always analytical platforms, and
The data either tolerates degraded integrity or is seldom updated/deleted.

Outside cases where both is true don't even think about breaking 1NF. Even where both are true, that's not enough to decide to go that way.

So go with your second approach

answered Mar 12, 2016 at 15:02

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

storing multiple values in column db design approach

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related