Appropriate way to store this kind of information

Question

I have to store the following information into a database, to be used by a .NET assembly. They are representing times that a certain color is active:

RED: 16:30 - 18:30 Mon to Fri Incl Bank Holidays
AMBER: 09:00 - 16:30 & 18:30 - 20:30 Mon to Fri incl Bank Holidays & 16:30 - 18:30 Sat & Sun
GREEN: 00:00 - 09:00 & 20:30 - 24:00 Mon to Fri incl Bank Holidays & 00:00 - 16:30 * 18:30 - 24:00 Sat & Sun

I've initially set off with a database schema like so:

Column Name ID Pk Null? Data Type Default Histogram Encryption Alg Salt

RED_WEEKDAY_START 14 Y NUMBER None
RED_WEEKDAY_END 15 Y NUMBER None
RED_WEEKEND_START 16 Y NUMBER None
RED_WEEKEND_END 17 Y NUMBER None
AMBER_WEEKDAY_START 18 Y NUMBER None
AMBER_WEEKDAY_END 19 Y NUMBER None
AMBER_WEEKEND_START 20 Y NUMBER None
AMBER_WEEKEND_END 21 Y NUMBER None
GREEN_WEEKDAY_START 22 Y NUMBER None
GREEN_WEEKDAY_END 23 Y NUMBER None
GREEN_WEEKEND_START 24 Y NUMBER None
GREEN_WEEKEND_END 25 Y NUMBER None

However a couple of problems with this:

Doesn't deal with bank holidays (at all)
Only allows one period of time for each colour (for example green being split up on weekdays)

So what would be a better way to go about storing this kind of data?

Bank holidays can change from year to year. You'll probably need a table of DateTime,HolidayName pairs that you'll need to keep up to date each year. — mbeckish, Feb 09 '12 at 14:38
All I need at the moment is to specify if that color is a bank holiday, the logic for working out if that happens to be active now will be dealt with elsewhere — m.edmondson, Feb 09 '12 at 14:39
If all you need is to be able to record multiple sets of settings per color, then follow @iefpw's advice - make a master table of colors, and a detail table of settings per color. — mbeckish, Feb 09 '12 at 14:45

Barracoder · Accepted Answer · 2012-02-09T14:52:41.600

Five Columns

Colour | Day | StartTime | EndTime | AppliesOnBankHolidays

(RED: 16:30 - 18:30 Mon to Fri Incl Bank Holidays) translates to

RED|Mon|16:00|18:30|True
RED|Tue|16:00|18:30|True
RED|Wed|16:00|18:30|True
RED|Thu|16:00|18:30|True
RED|Fri|16:00|18:30|True

AMBER: 09:00 - 16:30 & 18:30 - 20:30 Mon to Fri incl Bank Holidays & 16:30 - 18:30 Sat & Sun translates to

AMBER|Mon|09:00|16:30|True
AMBER|Tue|09:00|16:30|True
AMBER|Wed|09:00|16:30|True
AMBER|Thu|09:00|16:30|True
AMBER|Fri|09:00|16:30|True
AMBER|Mon|18:30|20:30|True
AMBER|Tue|18:30|20:30|True
AMBER|Wed|18:30|20:30|True
AMBER|Thu|18:30|20:30|True
AMBER|Fri|18:30|20:30|True
AMBER|Sat|16:30|18:30|False
AMBER|Sun|16:30|18:30|False

And I'll leave the third line as an exercise!

Feel free to normalise dates and colours if you really want to save on space. The Bank holiday question requires another table of bank holidays which you can join to in any query you use:

PSEUDO SQL

SELECT t.Colour, t.StartTime, t.EndTime
FROM Times t 
WHERE Day(Today)==t.Day AND NOT (t.AppliesOnBankHolidays==FALSE AND IsBankHoliday(Today))

Thanks Mark I think you're spot on there – m.edmondson Feb 09 '12 at 14:47 — m.edmondson, Feb 09 '12 at 14:47

score 0 · Answer 2 · answered Feb 09 '12 at 14:32

You have to create a table and a second table for each entry. There will be one to many relationship. You use the foreign key on the second table. Insert the main entry, get its primary key and for each key create the multiple rows for that key. One to many relationship. You have to break it down completely. Key here is primary key and foreign key generated by the databse.

score 0 · Answer 3 · answered Feb 09 '12 at 14:47

I would normalize this a little bit. First I'd have a table identifying the "colors":

fldColorID          | int
fldColorName        | varchar(28)
fldColorDescription | varchar(128)   (might not be necessary)

Then follow it up with a table containing the start/end times that are applicable:

fldSpanID               | int
fldStartTime            | datetime
fldEndTime              | endtime
fldIsHoliday            | bit
fldSpanDescription      | varchar(128)   (necessary from a human readability point)
fldColorID              | int  (FK)

This would give you the ability to have multiple colors added, and then have multiple spans applied to each color. It would also allow you to mark a specific span as a holiday span, and if you wanted you could add a different boolean to track a weekend span.

Appropriate way to store this kind of information

3 Answers3