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 08:33.
I'd rather be riding on the Tiger 800 or the Norton