How to keep track of hours?

[chobo321321]

Hi, I am trying to figure out a way to keep record of store hours, and days open for lots of different restaurants. The only thing I could up with is the following. I'm not sure how to keep track of store hours, or even the best way to make a table for hours. Any help is appreciated, thanks.


tblRestaurant
RestaurantID (pk)

tblRestaurantDays
RestaurantID(pk)
DayID(pk)

tblDays
DayID(pk)
DayName

tblHours
HourID(pk)
Time

 

[neileg]

It's not clear what you want to do.

Are you storing opening hours, e.g. Mon-Fri 10:30 til 19:00, Sat 9:30-18:00

Or, are you recording the actual hours open?

I wouldn't bother with tblDays. There are only 7 days and VBA assigns values to days anyway.

 

[chobo321321]

I'm trying to make it so I can store all hours of the day (am and pm), so when I query a database from a website I could display the store hours dynamically.
Ex: Mon(from day field) 9am(am field, I guess) to 5pm(pm field). I'm not sure if this kinda setup is a good design or not.

That brings up another question, would it be better to use military time?

 

[neileg]

Sorry but it seems you're making it harder than it needs to be.

tblRestaurant
RestaurantID (pk)

tblRestaurantOpening
RestaurantID(pk)
DayName Text
Opening Text
Closing Text

That would seem to do it. The data in tblRestaurantOpening would look like this:
RestaurantID 1
DayName Monday
Opening 9:00 am
Closing 5:00 pm

Of course if you want to do any arithmetic with the times, that won't work. If that is so, I would use this table set up:
tblRestaurantOpening
RestaurantID(pk)
DayName Text
OpeningHr Number
OpeningMin Number
ClosingHr Number
ClosingMin Number

I would record the hours on the 24 hour clock basis.

 

[chobo321321]

Ya, the problem is I might end up having to use the time field for queries, and stuff, so I thought I should make it more specific because that (in my opinion) would make it easier to program.

I ended up going with the following setup (it now includes holidays)


tblRestaurantTimes
RestaurantID
DayID
HourID

tblDays
DayID
DayName(inlcudes days of the week and holidays. 1-7 are weekdays, numbers after that are holidays)

tblHours
HourID
Hours (military time)

An example record could be the following.

RestaurantID             DayID                    HourID
4                            1                    08:00
4                            1                    16:00
4                            8                    11:00
4                            8                    15:00

The above shows a restaurant open Monday(1) from 08:00 to 16:00
and on Christmas(8) from 11:00 to 15:00. This is the best I could come up with if anyone sees any major flaw or problems with this setup, or has a better one please let me know. Thanks.

 

[neileg]

Two flaws. You haven't identified whether the HourID relates to opening or closing. Sooner or later that will trip you up.

Secondly, if your Hours field is a Date/Time field, remember that this will be treated as a date (sometime in 1898 if my memory serves me right). That's why I suggested you stored the time as numbers fields. Some systems use minutes only so that 11:00 would be stored as 660 minutes.

 

[chobo321321]

I finally get it. Ya, I see how over-complicated my way would be. I'll definitely use your method. Thanks a lot for taking the time to help, I really aprreciate it.