One to Many relashionships for a restaurants website?

Question

Each restaurant has restaurant branches, each branch must determine which days of the week it opens, each of those days must determine (several) open_hour and close_hour thru that day.

I created one to many relationship using these tables: rest_names ---> rest_branches ---> open_days ---> open_hours

Am I going right this way? or there is another way to do this, maybe less complicated? And how the query will be like to get the hours of a restaurant on a specific day, say sunday?

Answer 1

It depends on how you're storing the open days (I assume you're using a date value) but you could combine both the date and time in a single column as long as the data type for the DBMS supports it. This would eliminate having to join "open_hours" to "open_days".

Answer 2

You're on the right path. The entities I see are:

• Restaurant Chain (eg TGI Friday's)
• Restaurant (specific one);
• Opening Time (restaurant, day, open time, close time)

Having an entity for day seems unnecessary.

Answer 3

one restaurant names to many branches
many branches to many open days
many open days to many open hours

select [columnnames]
from open_days
join rest_branches on open_days.rest_branchId = rest_branches.rest_branchId
join rest_name on rest_name.rest_nameId = rest_branch
where open_days = 'sunday'

Answer 4

I would suggest storing the opening times, and then the time each restaurant stays open, instead of the closing time. This will make it easier to make calculations on the opening times, and it will also avoid the ambiguity with restaurants that stay open after midnight.

Example design, using MySQL:

CREATE TABLE restaurant_chains (
    id int AUTO_INCREMENT PRIMARY KEY,
    name varchar(20)
);

CREATE TABLE restaurant_branches (
    id int AUTO_INCREMENT PRIMARY KEY,
    chain_id int NOT NULL,
    locality varchar(20)
);

CREATE TABLE opening_times (
    id int AUTO_INCREMENT PRIMARY KEY,
    branch_id int NOT NULL,
    day_of_week int NOT NULL,
    open_time time,
    open_duration time
);

Make sure to add the relevant foreign key and unique constraints. You could also add a check constraint so that day_of_week is always between 1 and 7, since that would represent the weekday (1 for Sunday, 2 for Monday, etc).

Now let's fill our database with some test data:

INSERT INTO restaurant_chains VALUES (NULL, 'Chain A');
INSERT INTO restaurant_chains VALUES (NULL, 'Chain B');

INSERT INTO restaurant_branches VALUES (NULL, 1, 'Branch 1 for A');
INSERT INTO restaurant_branches VALUES (NULL, 1, 'Branch 2 for A');
INSERT INTO restaurant_branches VALUES (NULL, 2, 'Branch 1 for B');
INSERT INTO restaurant_branches VALUES (NULL, 2, 'Branch 2 for B');

INSERT INTO opening_times VALUES (NULL, 1, 1, '10:00:00', '04:00:00');
INSERT INTO opening_times VALUES (NULL, 1, 1, '19:00:00', '03:00:00');
INSERT INTO opening_times VALUES (NULL, 1, 2, '08:00:00', '12:30:00');
INSERT INTO opening_times VALUES (NULL, 2, 1, '19:00:00', '05:15:00');
INSERT INTO opening_times VALUES (NULL, 2, 2, '19:00:00', '04:00:00');

The following query returns the opening time, closing time, and duration for all restaurants:

SELECT  rb.locality,
        ot.day_of_week,
        ot.open_time,
        ADDTIME(ot.open_time, open_duration) AS close_time,
        ot.open_duration
FROM    opening_times ot
JOIN    restaurant_branches rb ON (rb.id = ot.branch_id)
JOIN    restaurant_chains rc ON (rc.id = rb.chain_id);

+----------------+-------------+-----------+------------+---------------+
| locality       | day_of_week | open_time | close_time | open_duration |
+----------------+-------------+-----------+------------+---------------+
| Branch 1 for A |           1 | 10:00:00  | 14:00:00   | 04:00:00      | 
| Branch 1 for A |           1 | 19:00:00  | 22:00:00   | 03:00:00      | 
| Branch 1 for A |           2 | 08:00:00  | 20:30:00   | 12:30:00      | 
| Branch 2 for A |           1 | 19:00:00  | 24:15:00   | 05:15:00      | 
| Branch 2 for A |           2 | 19:00:00  | 23:00:00   | 04:00:00      | 
+----------------+-------------+-----------+------------+---------------+
5 rows in set (0.00 sec)

Then the following query would return the opening hours of a specific restaurant, on a specific day:

SELECT  ot.open_time,
        DATEADD(ot.open_time, open_duration) AS close_time,
        ot.open_duration
FROM    opening_times ot
JOIN    restaurant_branches rb ON (rb.id = ot.branch_id)
JOIN    restaurant_chains rc ON (rc.id = rb.chain_id)
WHERE   rb.id = 1 AND ot.day_of_week = 1;

+-----------+------------+---------------+
| open_time | close_time | open_duration |
+-----------+------------+---------------+
| 10:00:00  | 14:00:00   | 04:00:00      | 
| 19:00:00  | 22:00:00   | 02:00:00      | 
+-----------+------------+---------------+
2 rows in set (0.00 sec)

Answer 5

From a data model design, it is not strictly necessary to have an ID column as the primary key in the tables for chain and branches: you may have to define a unique constraint for some other column (like for the chain name, for example), and that column may perfectly be the primary key. The ID column would be a surrogate key, and you may decide to have it or not. Some people consider it a good practice to always have such a primary key, but it is not strictly necessary. You may even find that in many cases it is not worth having it.

Answer 6

You could consider having the restaurant name, day-of-week and opening hours in a single table.
EG. Freds Diner/Monday/10.00/22.00/Tuesday/11.00/21.00/ etc. And before you send the Normalization Police around, please note that full integrity is maintained in this structure. It is unlikely that anyone is going to create a new day of the week in the foreseeable future.
Considerations on whether to use this structure relate to how easy it is to programmatically access/read/update the information. You could also include the restaurant address in the same row if required. Note. 'Address' is an an Attribute TYPE - not an attribute per se. It is a COMPOSITE attribute type as is DATE. NAME + Attribute Type = Attribute EG INVOICE + ADDRESS = Invoice Address and INVOICE + DATE = Invoice Date. Invoice Address and Invoice Date are both single composite attributes and follow the same rules. The main issue regarding the data structure is DATA INTEGRITY - normalization is simply a way to try to ensure the integrity of data.