Db Design for Restaurant operating time

Databases
1

Need help in designing a db to store the operation time of a restaurant.

Opening days - monday thru sunday
Timings - closed for breaks after lunch and saturday timings etc.

2

Sorry to hijack your thread,

This answer my question too but how would you select day and time from the frontend?

Any idea what UI could look like for a day of week and time

3

day VARCHAR(9),
timestart TIME,
timeend TIME

nothing says day has to be unique.

4
Off Topic:

didnt quite get the part “is the best time”

5

just curious … whats r937… :smiley:

6

better to have only one table for people

7

no, in this case it is

PRIMARY KEY ( restid, weekday, opentime )
8

Thanks you.

Have another question.

There is a USER. who can tweet (status Messages )

a User can be assigned ownership of a restaurant and can tweet Rest status messages also but as a rest.

Is it better to have users seperately to rest owners or is it ok to make rest owners also as users.

9 
CREATE TABLE opening_hours
( restid INTEGER NOT NULL 
, weekday TINYINT NOT NULL
, opentime TIME NOT NULL
, closetime TIME NOT NULL
, PRIMARY KEY ( restid, weekday, opentime )
);
INSERT INTO opening_hours VALUES
 (21,1,'10:00','20:00')
,(21,2,'08:00','22:00')
,(21,3,'08:00','22:00')
,(21,4,'08:00','22:00')
,(21,5,'08:00','22:00')
,(21,6,'08:00','22:00')
,(21,7,'09:00','21:00')
,(44,2,'09:30','18:00')
,(44,3,'09:30','18:00')
,(44,4,'09:30','18:00')
,(44,5,'09:30','18:00')
,(44,6,'09:30','18:00')
;

the sample data is for two restaurants – the first one, restaurant 21, is open 7 days a week, while the second one, restaurant 44, is open only monday-friday

10

i am a little confused. u mean natural key in this case is rest_id ?

11

two rows

12

thanks buddy.

the problem is… some rest open 10am to 4.30pm then 7pm to 11pm… was wondering how to accomodate that. ;(

13

Some rest will vary the timings according to the day. so need to accomodate that.

14

[ot]937 is just the sweetest number in the whole world… as well as being a prime number

9:37 is the best time, too (see my avatar)[/ot]

15

@r937 when should a table have an id - unique and autoincrement ?

i use it on all tables. is there a reason u havent used it in ur example ?

16

use a surrogate key (which is what an auto_increment is) only when there is no suitable natural key available for the purpose

17

As i said… day doesnt have to be unique.
(21,4,‘08:00’,‘12:00’)
(21,4,‘14:00’,‘22:00’)

The above restaurant is open from 8 AM till 12, and 2 PM til 10 on day 4 (which is… Wednesday, if my memory of the date function is accurate)