Best Practice

[James Harle]

I am developing a web based e-learning application and I am presently working on the administrative side and the resulting database. There are specific businesses rules that I have been asked to include and because I am a newbie to SQL I would appreciate some advice on the best way to design the database to accommodate these. The design must accommodate for the following four entities…

1) Company – the head office for the e-learning company. This will not perform any hands-on learning, but is really for administrative and analysis purposes. The application can support multiple-companies, where various organizations register to have their company use the learning services.
2) Location – this is essentially the learning centre that would be affiliated with a particular company. There can and will be many locations for a single company, usually representing physical locations.
3) Teacher – represents a teacher/tutor that is working at a particular location. There will be many teachers associated with a particular location and they may (not confirmed yet) be required to move from one location to another depending on the demand in one location versus another.
4) Student – each student can be associated with many teachers (i.e. one for Math, English etc.)

The caveats in this design are that the application must also support the single user student who can register for the e-learning services on their own and not be affiliated with a Company, Location or Teacher. They can run standalone, but can have the option to be part of the standard hierarchy should they require to be assigned to a teacher at a location for a particular company.

I had planned on defining the tables like so to accommodate these requirements using composite keys where need be…

Company
PK CompanyID
CompanyName


Location
PK CompanyID
PK LocationID
LocationName

Teacher
PK CompanyID
PK LocationID
PK TeacherID
TeacherName

Student
PK CompanyID
PK LocationID
PK TeacherID
StudentName

In the standalone student scenario the student table entry would have NULLS in the ComapnyID, LocationID and TeacherID. Is this type of table design appropriate or should I use Foreign Keys to establish the links to the subsidiary tables as show below.

Company
PK CompanyID
CompanyName


Location
PK CompanyID
LocationName
FK LocationID

Teacher
PK CompanyID
TeacherName
FK LocationID
FK TeacherID

Student
PK CompanyID
StudentName
FK LocationID
FK TeacherID

Any help in this regard would be appreciated

[Joar Swenning]

A question rather than an answer:
Are the students really directly associated to the teachers and locations? Seems to me that the teachers' being able to be related to more than one location and vice versa implies an "Assignment" entity between teacher and location. If so this would be the entity that the students may be related to.

I have attached a RISE-model file to illustrate. Note that i have only modeled it to show this aspect and nothing is done about types, uniqueness or any other aspect you would have to consider.

If you dont have a RISE Editor you can download it (its free) from:
http://www.risetobloome.com/clickonce/riseeditor/riseeditor.htm