Scheduling System Database
-
hello.. i'm looking for advice in constructing my database (using wamp server) in Room Scheduling System
Basically the input is a Class Schedule..
ex.
math1 8-9am monday
eng1 9-10am monday
computer1 8-9am tuesday
up to 8+ subjects (or i can make this optional).. for the whole week (monday - saturday)
after the input..
-the system will automatically plot the class schedule in each possible rooms available.
-with no time/room conflicts with other schedule/subject..
-rooms have room type and subjects have subject type..
-rooms can only be used if room type = subject type..
-if the subject inputed has no available rooms at all.. the system will put that subject in a somewhat 'special area/unscheduled area'.. something like that.. still the whole class schedule is accepted..
i need some help with the database.. i'm struggling thinking how to eliminate time conflicts in every room and in every day..
hope you can help me..
-
First question right off the bat: how much do you know about data normalization? (To anyone else reading this: does anyone have any articles about normalizing data they can post?)
What I would do is I'd set up the classes in one table (call it CLASSES or SUBJECTS or something like that) where you'd put your class info, such as name, description, course number, etc. Do NOT include class schedules/dates/times in this table!!!
I'd set schedules as a separate table. I'd probably also set classrooms in a separate table.
I'd tie them all together in a master class schedule table or even a view -- something that does a join on all the tables so you end up with class, schedule, and room together.
Hope this helps!
Edit: Here's some reading material for normalizing data -- hopefully, they'll help you out.
- A Normalization Primer [/url] (SSC article)
- Database normalization (Wikipedia article)
- What is Normalizations? (SSC forum question)
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/ -
To tell you the truth.. I am completely clueless with normalizations. But I do understand the concept. I've already finished a working program in netbeans w/out losing data integrity and not fully understanding normalizations.. O.o
Here is my idea of tables..
room
r_no
r_starttime
r_endtime
r_day
r_type
subject
s_id
s_desc
s_type
s_starttime
s_endtime
s_day
schedule
sched_id
s_id
r_no
please do correct me if anything is wrong.. i'm still new at this and willing to learn.. =)
-
I would remove ALL references to dates/times in the room and subject tables, and put them in the schedule table, maybe something like this:
room
r_no
(remove these)
r_starttimer_endtimer_dayr_type
(Note: if you're dealing with multiple buildings, you might need to add more columns to account for that
subject
s_id
s_desc
s_type
(remove these)
s_starttimes_endtimes_dayschedule (I'd completely redo this table, something like this -- note that I'm using "t" to indicate "time":)
t_id
t_day
t_starttime
t_endtime
Then, I'd tie it all together and put it into a junction table that might look something like this:
ClassSchedule
section (identifier, since I'm assuming you might have different instances of the same class subject)
s_id (from subject table)
t_id (from schedule table)
r_no (from room table)
I'd suggest trying to learn as much as you can about data normalization -- it'll go a long way to helping you understand this better.
Good luck!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/ -
Ray K (10/28/2010)
I would remove ALL references to dates/times in the room and subject tables, and put them in the schedule table, maybe something like this:room
r_no
(remove these)
r_starttimer_endtimer_dayr_type
(Note: if you're dealing with multiple buildings, you might need to add more columns to account for that
subject
s_id
s_desc
s_type
(remove these)
s_starttimes_endtimes_dayschedule (I'd completely redo this table, something like this -- note that I'm using "t" to indicate "time":)
t_id
t_day
t_starttime
t_endtime
Then, I'd tie it all together and put it into a junction table that might look something like this:
ClassSchedule
section (identifier, since I'm assuming you might have different instances of the same class subject)
s_id (from subject table)
t_id (from schedule table)
r_no (from room table)
I'd suggest trying to learn as much as you can about data normalization -- it'll go a long way to helping you understand this better.
Good luck!
i put start time end time and day in rooms and subjects because the system will automatically look for the appropriate rooms in every subjects. w/out time conflicts.. meaning the user will not input the room number..
can i still use this table structure?
i'm sorry if i'm slow at this.. XD
-
silfolion009 (10/28/2010)
i put start time end time and day in rooms and subjects because the system will automatically look for the appropriate rooms in every subjects. w/out time conflicts.. meaning the user will not input the room number..NO. You absolutely do NOT want to put dates and times in multiple tables, because this violates the purpose of data normalization. If you have those values in multiple tables, you are creating more problems, not less. You actually increase the chance that you're going to have conflicts.
can i still use this table structure?
Yes. Instead of having the system search the subjects table, it needs to search the junction table.
Do you have any control over the system that does the search?
i'm sorry if i'm slow at this.. XD
No big deal! I've been in your shoes before. We all learn!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/ -
Rooms don't have start and end times (unless they only physically exist for certain times and vanish at others)
Schedules have start and end times. Booking slots have start and end times. Classes have start and end times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (10/28/2010)
Rooms don't have start and end times (unless they only physically exist for certain times and vanish at others)Schedules have start and end times. Booking slots have start and end times. Classes have start and end times.
True, but it looks like he's trying to avoid room conflicts as well; hence the reason for room schedules.
My thinking is that he keeps rooms, classes, and time slots separate so that he has single data points for each entity. He can then join them together and be able to query for possible conflicts that might come up.
Does that sound feasible, or is there a better way?
Edit: Whoops, "booking slots." That works too!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/ -
Thank you for the replies guys.. Especially Ray.. I appreciate your help.. I'll ask questions again if I need some advice.. thanks again =P
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply