Post reply

Diaries and Scheduling

  • August 9, 2007 at 6:55 am

    #175826

    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!

  • August 9, 2007 at 8:14 am

    #725735

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 9, 2007 at 8:38 am

    #725745

    Ah, yes, should've seen that! Thanks.

  • August 10, 2007 at 5:14 pm

    #726243

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