A hint to create a db schema

Question

I need to realize a database in this mode:

TABLE restaurant

with "classic" fields of a place: address, street number, phone, website, etc etc etc.

I need to indicate also the days of opening // closing. E.g. From Monday to wednesday close, saturday only evening and sunday lunch + evening.

So, I did think at another table with 14 rows:

ID_RESTAURANT  MONDAY_LUNCH MONDAY_EVENING [...] SUNDAY_EVENING
    12         0             0                    1   

And "finally" every restaurant has his own cooking ("international", "chinese", "japan")

Another table with a column for every cooking?

Finally, several services. "Conditioned Air", "Credit Card", "Animals permitted" "children addicted" etc etc.

Another table with a column for every "service"?

ID_RESTAURANT    CONDITIONED    CREDIT_CARD     ANIMALS
    12              1              1              0
    13              0              1              1

I hope that I'm clear. Thank you very much!

Answer 1

TABLE Service (SERVICE_ID, SERVICE_NAME, ...)
TABLE Restaurant (RESTAURANT_ID, RESTAURANT_NAME, ...)
TABLE RestaurantService (RESTAURANT_ID, SERVICE_ID)

In short, if the field is part of the key or depends on the key of the table, you are doing it right. Read more on the very straightforward normalization link MichaelT provided already.

Answer 2

There's a joke I heard some time back.

If you ask a C programer to count to 10, you get '0, 1, 2, 3, 4, 5, 6, 7, 8, 9'.
If you ask a DBA to count to 10, you get '0, 1, many'.

The key to the understanding of this joke is that if you ever are dealing with more than one thing in a database, you really need to have it be a many... not a 2 or a 3. There are many many's described in this database that should be treated as such.

       +-----------+   +-----------+     +------------+
       | restaurant|   | rest_time |     | meal       |
       |-----------|   |-----------|     |------------|
   +-->| id        |<--+ rid       | +-->| id         |
   |   | name      | | | day (enum)| |   | name       |
   |   +-----------+ | | mealid    +-+   +------------+
   |                 | | open (b)  |
   |   +-----------+ | +-----------+
   |   | phone     | |
   |   |-----------| | +------------+    +------------+
   |   | phnum     | | | rest_serv  |    | service    |
   +---+ rid       | | |------------|    |------------|
       | type      | +-+ rid        | +--> id         |
       +-----------+   | sid        +-+  | name       |
                       | has        |    |            |
                       +------------+    +------------+

You have a restaurant which has some attributes. Things that the restaurant can only have one of are columns in that table. It can only have one name, and one id. Depending on the description, it may have only one address (though if you have a chain like McDonalds). Other things it has only one of are things like capacities.

A restaurant may have multiple phones (fax your order in vs reservations?) And phones only belong to one restaurant. This is a one to many relationship.

The restaurant also has some services - but all restaurants also may have those same services. You want to be able to do a query of 'what restaurants has air conditioning?'

Likewise, a restaurant also has some meals that it serves on some days. I've got a simplified approach that works with mysql there for days, but having days be a separate table wouldn't be inappropriate and would be entirely appropriate if you have things like holidays being called out specially (new years eve: lunch, no dinner).