Display time table

[anand01]

hi all,
i need to display time table for students and staff my table design looks like

--------------|
timtable
--------------|
classid
hour
subjectcode
day
--------------|

--------------|
Working hours
--------------|
hour
starttime
endtime
--------------|

--------------|
timtable
--------------|
classid
hour
subject code
day
--------------|

--------------|
subjectmaster
--------------|
subjectcode
subjectname
subjecttype
.
.
.
--------------|

--------------|
staff alloction
--------------|
subjectcode
staffcode
--------------|
now i need to combine all these tables and display the time table could any one guide me to do this ..
thanks in advance

[StephNicolaou]

Firstly, you're missing the master data for the people attending the lecture table. You can either have 2 tables; Student Master and Staff Master or a Persons table with a Student/Staff drop-down menu from a PersonsType table.

2. You don't really need two TimeTable's, you can add a Join table between Persons and TimeTable, thus called Persons_Timetable. This should have the fields PersonsID and ClassID. Then you can either show the timetable for one particular student or one particular staff member with all the students they are teaching. Depends on how you would like to write your query.

3. The Working Hours table. You could just add a lengthTime to the SubjectMaster table and work out how many hours the staff member has worked by the subjects they are assigned to. However, if you want it to be like business real-world clocking system in and out of lectures (which is not how the education industry works but whatever), you should add the PersonsID field to the Working Hours table assuming their ID card is coded with their PersonsID card in the DB.

4. The subjectCode in the TimeTable and StaffAllocation table should be a foreign key of the SubjectMaster assuming that you would still like to keep the StaffAllocation table instead of my suggestion of the Persons_Timetable to allocate them together.

Just Remember, a table holds data solely related to that entity/ thing. When it's field like subjectcode is used in another timetable, that isn't a part of the timetable class, it is a foreign link; subjectCode is part of the SubjectMaster entity. And when two entities share another related entity, use a join table.
Although it's up to you how you want to manage your entities. Hope that helps.

[anand01]

thanks a lot for your reply StephNicolaou