creating a data base for staff time table?

Question

i wana create a database for staff timetable!! my attributes are,

StaffTimeTable - TimetableID(pk),StaffID,periods,day.

per day a school has 8 periods(where a staff need to attend) one staff can have only one timetableID.

My problem is how can i create a database with one timetableid for 5days!!

an exmaple

TimeTableID    StaffID  Day     period1 p2 p3     p4  p5   p6   p7   p8 

stt001         st001  Monday  maths      physics 

My Question is How can i insert the Tuesday TimeTable(for the same time table id) ??

If answered its most appreciable !! :)

Answer 1

you should make a few tables for this.

SEE IT IN ACTION!

SETUP:

CREATE TABLE periods (
   id int, 
   period varchar(55)
);

this table would look something like this

id       period
1        'maths'
2        'physics'
3        'english'
... etc

you should also have a table for the days like so

CREATE TABLE day_of_week (
    id int,
    day varchar(55)
);

this would look something like this.

id             day
1              'Sunday'
2              'Monday'
3              'Tuesday'
4              'Wednesday'
.... etc.

you should have a staff table like so

CREATE TABLE staff (
    id int,
    staff_name varchar(55)
);

this table would look something like this.

id            staff_name
1             Mary
2             John
..... etc

NOW you need to have a connecting table.

CREATE TABLE StaffTimeTable (
    id int, -- this is the pk
    staffID int,
    periodID int,
    dayID int
);

NOTE:

each of these id's are the foreign key to the primary keys in your other tables... also all of the primary key id's should be auto incremented.

COMBINE IT ALL

SELECT t.id, s.staff_name, d.day_name, p.period
FROM StaffTimeTable t
JOIN staff s on s.id = t.staffID
JOIN day_of_week d on d.id =  t.dayID
JOIN periods p on p.id = t.periodID
ORDER BY t.id;