Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013
    Posts
    5

    Red face Unanswered: school timetable in access

    hi guys
    i have a question regarding my table.. i got problem with my table under Timetable. Is there any solutions to find whats the exact primary key for Timetable. I understand once we set as primary key, it cant be duplicate. at moment i set my primary key (PK) as "DAY"
    for example: when i try add data field on timetable using on Form window:

    Timetable Table
    Day(Pk) GROUP CODE SESSION TIME UNIT TITLE INSTRUCTOR
    MONDAY KG/01 Morning 8:30-9:00 ABC LEARN ALAN
    MONDAY KG/01 morning 9:00-9:30 ART Felix

    from that example i got problem, monday cant be duplicate. is there any replacement to add new field? or maybe my table need to adjust again? help me
    thanks
    Attached Thumbnails Attached Thumbnails timrtable.jpg   timettt.jpg  

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You could make both the day and time fields the key (a compound key), or add an autonumber field and make it the key.
    Paul

  3. #3
    Join Date
    Feb 2013
    Posts
    5
    oh u mean add an auto no example index as autonumber

    so thats mean under my Timetable Table Field has:
    index (Primary key)
    Day
    group code
    Session Time
    Unit Title
    Instructor
    Venue

    is that correct what u mean right?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That was the second suggestion, yes.
    Paul

  5. #5
    Join Date
    Feb 2013
    Posts
    5
    thanks
    oh yea.. if i didnt put any primary key or index as primary key under Table: Timetable is it ok? since timetable are all repeating... then when i create timetable report, i can easily group them all in one..
    Timetable Table:
    -------------------------
    Day
    group code
    Session Time
    Unit Title
    Instructor
    Venue
    --------------------

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how you model the primary key is up to you
    you can use an autonumber column or a composite of several other existing columns

    using an autonumber column, a so called surrogate kley, is fine where there is no obvious existing column/columns that would uniquely identify a row. using existing column/columns are referred to as natural or candidate keys.

    ideally you should use a natural key if available, but only if it makes sense. reasons not to use a natural key would be its very long, one or more elements are liable to change (changing values of columns that make up a PK, especially if that column is used in another table as a FK is a BIG no no), or for performance reasons, or if you needed up breaking some internal limit inside the db. as with all 'rules' it depends, and if you have got a good reason to break it, then break it, but recognise why you are breaking it.

    in your case the easy answer if to use a an autonumber key, but it then measn that you have to implement in logic the natural key constraint. IE you cannot have more than one class/session for a specific group at the same time.

    you could include the day,group and time in the primary key,
    however a problem with that is thge data entry, how do you stop overlapping, say one one session is 45 minutes another is 30 minutes, its tricky to make a uunique constraint in the DB using start and or finish time. ferinstance 08:00..08:59 and 08:15..08:45 are disitnct peridos of time, nbut overlapping... there is no way that Im awre of to force that constrain in the db by indexes alone.

    but that too is an issue if say sessions could be variable time. if they were say 1 hour blocks you could include the start time. a way round that is to use time blocks (divide you day up into blocks of time and give those blocks an ID eg 08:30..08:44 = 1, 08:45..08:59 and so on.
    then allocate rooms in blocks, if you had a 15 minute time block and a one hour class that would require 4 bookings for one hour.

    personally for this sort of scheduling problem I think I'd probably want to fiudn a dark smoke filled room, with a kettle and lots and lots of tea/coffee on hand, a whiteboard or plenty of postit notes and do it manually.

    yes it can be done but Im not sure the effort and time take woiuld be worthwhile. by all means store the data in your DB after the evenbt but do the scheduling manually. if you do do the scheduling manually on paper/whiteboard then defien your primary key as an intersection table


    you have an entity for classes (eg year 1 receptionj)
    an entity for rooms (eg assembly hall)
    an entity for Subjects (eg singing)
    then an intersection tabel which ties all 3 together
    comprising the primary keys from classes,rooms,subjects AND the time

    if you need to allocazte rooms dynamically per week then you also need to know the actual date and time of the proposed booking.

    some times manual systems are far more appropriate than electronic systems
    Last edited by healdem; 02-06-13 at 09:33.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •