Best way to set up this database

[wemustdesign]

Just need a little advice on the best way to do this

 

- I have created a datebase to organise courses. First I have created a table CourseType, this holds the name and other details of the course.

 

Table CourseType

id, name, outcomes, skill levels

 

- Next I have created CourseDates. This will hold the dates of the course.

Table CourseDates

id,  CourseType, location, places

 

Next is where I am not sure. I now need to enter which students are on the course (I have already set up a table for students). Would it be best to enter the students participating on the course in the CourseDates table? This would have to be done using implode with the students username like: 12 | 22 | 17

 

or would it be best to set up a seperate tabe like:

 

Table Courses

id, syudentID, CourseType

 

Any advice on best practice would be appreciated.

 

 

 

[fenway]

One row per tuple -- always.

[DaiLaughing]

You definitely need a table for every different set of data.  That's not always clear but most of the time common sense works.  You are starting well but any time you are thinking of multiple items of data ending up in the same place think again.  For example in your courseDates table you have "places".  If the course instance can be in more than one place ask yourself whether that is actually two course instances.  If not you need a related table for places so each course instance can have many places.

 

I once designed a data structure for a similar purpose and from memory it had 12-15 tables before we even looked at admin functions etc..

 

qualification, course (some courses have more than one qual), course instance, locations, staff, students, enrolments (students can have more than one course instance and each course instance has many students) and so on.

 

This is a pretty nasty one to do (have a look at most school/college systems to see how not to do it) so if it's not by your choice you should probably complain to the nasty person who told you to do it!