CODEWITHSUNDEEP
c#databaseoracle-databasedatabase-designrelational-database
m.edmondson
m.edmondson

Reputation: 30922

Appropriate way to store this kind of information

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:

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

Upvotes: 1

Views: 124

Answers (3)

Barracoder
Barracoder

Reputation: 3764

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))

Upvotes: 2

Joel Etherton
Joel Etherton

Reputation: 37543

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.

Upvotes: 0

iefpw
iefpw

Reputation: 7052

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.

Upvotes: 0

Related Questions