CODEWITHSUNDEEP
database-design
Al Grant
Al Grant

Reputation: 2354

Database schema - days of the week structure

I have a tables that represent shifts (Day, Afternoon, Night) and days of the week, and locations.

I want to be able to query this data so for a given shift, on a given day of the week at a given location I can know how many staff are needed to cover the shift.

My data looks like:

Table_shifts
1  Day
2  Afternoon
3 Nights

Table_dow
1 Mo
2 Tu
3 We
4 Th
5 Fr
6 Sa
7 Su

Table_locations
1 Ventura
2 SanDeigo
3 OrangeCounty

But, most of the time the number of staff required at a location does not change with the shift or the day of week. Sometimes it is all days, Sa-Su or M-F.

Should I keep day of week 1-7,and have a record for every shift, for each day of week and each location or to how about bitwise like here for dow?

Fiddle here.

A typical query would be : How many people needed to work on a Sunday Afternoon at Ventura?

Upvotes: 0

Views: 278

Answers (1)

James K. Lowden
James K. Lowden

Reputation: 7837

What you're asking is a kind of time-series question. In general, if your table has two columns, from_day and until_day, then you can store information that belongs to a range of days. If it turns out that some situtation applies to only one day, no problem: the two columns have the same day (say, from Wednesday until Wednesday).

You can represent each day separately, too. It just means that to make changes, the application updates sets of rows representing the range, instead of a single one.

Probably you're going to want to represent the days as 0-6 or 1-7, depending on your system. Otherwise the DBMS has no way to know that Wednesday falls between Tuesday and Thursday: the day names have no intrinsic order.

Upvotes: 1

Related Questions