Diaries and Scheduling
-
This is following on from my previous topic, the same database anyway.
I am now designing the scheduling part of the database, a common requirement. I was just wondering how I should structure it for ease of programming.
I have a diary table with the engineers appointment times thus:
ID_diary
...
DiaryDateTime [datetime]
DiaryLengthOfCall [decimal(5,2)]
FK_Engineers [int]
FK_Jobs [uniqueidentifier]
To explain the possibly obvious, each FK_**** column is a foreign key.
Is this OK? A job may have more than one diary appointment with different engineers. I seems to me I may have to separate this out or have got the FK/PK relationship the wrong way.
I don't mind admitting my head hurts!
-
First I wouldn't prefix things. Never know what might happen here and then you're messed up. I'd say DiaryID, EngineerID, etc. That way someone isn't asking if FK_Engineer matches to EngineerID or something else.
A "diary" is an appointment, right? If it can have multiple engineers or be for multiple jobs (never know), I'd set this table up as
create table Diary(
DiaryID,
DiaryStartTime
DiaryEndTime (or length)
)
Include other relevant fields that occur only for the appointment.
I'd then create two tables.
create table DiaryEngineers
( DiaryID, EngineerID)
to handle the mutliple engineers to a diary. Conversely you can easily see which appointments an engineer has.
I'd do the same thing with Diary's to jobs since you might get double ups, etc.
-
Ah, yes, should've seen that! Thanks.
-
... and don't forget to include building stored procedures in your design effort! Yes, these may change once your database design hits the 'real world' of developers, but by having the SP in place beforehand it's easier to enforce the 'only access the data through a SP' paradigm.
Just a thought!
Steve G.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply