0

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

  1. To have a multiple values in a single column (which is a bad idea)

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

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
super
  • 53
  • 8
  • Option 1 violates [1NF](https://en.wikipedia.org/wiki/First_normal_form), which as you say is [a bad idea](http://stackoverflow.com/a/3653574). Go with option 2, but don't duplicate `hotel_name` in each table (which is a violation of [2NF](https://en.wikipedia.org/wiki/Second_normal_form))—just keep it in the `hotel_main` table alone. Read up on [database normalisation](https://en.wikipedia.org/wiki/Database_normalization) more generally. – eggyal Mar 12 '16 at 08:42

1 Answers1

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:

  1. They are always analytical platforms, and
  2. 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

Chris Travers
  • 25,424
  • 6
  • 65
  • 182