MySQL Database Design – Clarification for Database Creation and Relations

database-designdbmsMySQLnormalization

I've trying to make database with few tables and relations between them. As I'm not very good in databases at all I need some clarifications. The idea is to connect table with table_1, table_2 and table_3 Here are two options:

Option 1 – table is directly connected to other tables and in query will be joining them.
enter image description here

Option 2 – will use middle tables to connect them with foreign keys..
enter image description here

Currently my database schema is a mess I have relations with both methods. Some are with middle table and some are without. This is my current view of relations which I think is not very good.
enter image description here

In option 3(current) you can see that connection are double – one from middle table and one directly. What is the best solution here?

Les's say:
table = hotel, table_1 = rooms, table_2 = reservation and table_3 = food.

When user make reservation the system shows him free rooms. He can also see what kind of food has restaurant in this hotel.
So hotel table should be connected with rooms, food and reservation.

update:
One of the query's that I use to show meals is

    $hotels_id = $_GET['meal_id'];
    $strSQL = "SELECT m.meal_id, meal_name, meal_image, meal_image_big, meal_weight, meal_price, meal_description 
        FROM meals m 
        JOIN meal_hotel mr 
        ON m.meal_id = mr.meal_id 
        WHERE hotels_id = '$hotels_id'";

So the table meal_hotel has two columns meal_id and hotels_id and this two columns are FK for hotels and meals. Am I right?

Best Answer

Just for a start:

Usually, this should become clear from normalizing the data. You need a separate table for each entity. And you require an intermediate table for many to many (m:n) relations.

With hotel and room/reservation, it's unlikely that a specific room/reservation relates to more than a single hotel. For meals, you need to decide, what exactly defines one. If all you care is, e.g., whether or not cheese sandwiches are served in some particular hotel, it'd look like m:n. You will need to ask you(r domain expert), what will be true for the lifetime of the system, you are working on.

Foreign keys will be needed, no matter, whether you have intermediate tables or not - the difference is, where they are stored.

Looking at your current schema: Two things come to mind. Either you are storing optional attributes in the intermediate tables - and you take the shortcut, if no optional attribute is filled in. Or you do actually have multiple relations between these entities. You would need to check your data to differentiate between the two. I'd usually vote for consistency - thus using one approach for storing a particular type of fact.

Update regarding "hotel_meal": With all this detail in the meal record, I'd doubt one dish to be offered in more than one hotel (unless you have hotel chains in your system, or several hotels' kitchen is run by the same caterer).
You could check:

SELECT
  meal_id
  , COUNT(hotels_id)
FROM meal_hotel
GROUP BY meal_id
HAVING COUNT(COUNT(hotels_id)) > 1;

If it returns any record, you have a reason for an intermediate table - if you decide the records returned do actually make sense in your domain (hotel chains, same caterer - you should know it).

Update II regarding "hotel_meal": If no meal is ever served in more than a single hotel, you should not need the additional table; just have the hotel_id as foreign key in the meal table. Just as with rooms and reservations…
To list all meals for a particular hotel:

SELECT * FROM meals WHERE hotel_id=1;

Feel free to come back with more detail.

Related Question