courses database design

babygodzilla

hi all

right now i have THE WORST database schema ever. i have a database that lists all the courses at our school, and a large script that composes a schedule. this involves taking in input from users, comparing possible combinations for time-conflictions, and so on.

right now i have ONE table and it feels so inefficient and sad... 🙁

basically the one table CLASSES has all the information of a class in it, such as classID, course name, teacher, room #, time, etc etc

can anyone teach me how to make a better design?

thx so much

eoghain

Well you'll need to look at your data and see what data is unique to each new table that you want to create. Just off the top of my head I'd think that you'd need these tables:

Courses - name, id, description of each course offered
Teachers - name, id, description of each teacher in the school
CourseTimes - time, room#, courseid where a given course is to be held

Now you might also need some cross referance tables. But to know which ones you'll need you'll need to ask yourself some questions.

Do teachers teach multiple courses?
Can different teachers teach the same course?

It'd be helpful to see the current schema to know what other datapoints you are using and will need to keep track of.

babygodzilla

thx for your reply!

i dont know if i need a too elaborate database, not that i can get that much info. see im just doing this as my own project since i think my school has a bad system in terms of helping students make their schedules. i only have access to data they publish online, and the only thing i have is the course list that is offered in one quarter. this course list includes:

course ID
course Code
course number
course section
course name
teacher
days
times
building
room
capacity
final exam code

here's a quick summary of what my script does:
1. You input the courses you want in a form
2. Script searches for the classes. If all classes are found, check every possible combinations and filter out the ones that have time conflicts.
3. display the good schedules (w/o time conflicts) in a day-and-time table.

i'd like to show you my schema but im not sure how to do it since i dont have shell access from my server. can i d/l the schema using phpMyAdmin, or FTP? well anyways, the schema is very simple. its just one table 'classes' with those fields named above (course ID, course code... final exam code) as columns.

thx so much!

eoghain

Well, since you are dealing with someone elses data I think you might just be stuck. Unless you want to spend the time to enter all of the information by hand, or write a complex tool to separate out the data into more logical tables.

babygodzilla

yeah i was afraid so. well it works right now, i just think the design stinks...