0

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!

  • 2
    You should probably read about normalization – user40980 Jan 8 '14 at 18:48
  • @MichaelT I'll look bad to you, but your answer does not help me at all. I know normalization, which is why I asked for help, to try to normalize the database as much as possible and since I can not find a pattern at the moment ... – sineverba Jan 8 '14 at 19:14
  • Ask a C programmer to count to 10, you get '0, 1, 2, 3, 4, 5, 6, 7, 8, 9', ask a DBA to count to 10 and you get '0, 1, many'. You've got 14 rows that describe the same thing, thats many. You've got a table that has 3 attributes of services? that's many. Build one to many relationships rather than stuff them into columns. – user40980 Jan 8 '14 at 19:16
  • @MichaelT maybe do you mean "many to many"? Please, look at this example if is it right for my model. phpknowhow.com/mysql/many-to-many-relationships – sineverba Jan 8 '14 at 19:35
  • Every restaurant could be have one, zero or many services... and one, or many, opening / closing... – sineverba Jan 8 '14 at 19:42
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.

  • OK, and sorry, but using a third table, is it many to many or not? – sineverba Jan 8 '14 at 19:46
  • In this example, yes it is describing a many to many relationship between Service and Restaurant. But that isn't the cure-all relationship for all your database design concerns in the question. But as you will see when you get into the phsyical deployment of your design, there is no "many to many" concept. There is only 0, 1, many idea that MichaelT already stated. – Michael O'Neill Jan 8 '14 at 19:48
  • It's many-to-many, structurally: One restaurant can have many services; one service can be tied to many restaurants. – Brian Jan 8 '14 at 19:49
  • Its a 1:many relationship from "Service" to "RestaurantService", and a 1:many relationship from "Restaurant" to "RestaurantService"; these two "1:many" relationships resolve the logical "many:many" relationship between Restaurant and Service. – Doc Brown Jan 8 '14 at 19:57
5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.