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 any 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.)

I need to perform extensive analysis on the performance of single student, students of a particular teacher, students of a particular location and the students belonging to the overall company.

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 Table
--------------------
PK CompanyID


Location Table
--------------------
PK CompanyID
PK LocationID


Teacher Table
--------------------
PK CompanyID
PK LocationID
PK TeacherID


Student Table
--------------------
PK CompanyID
PK LocationID
PK TeacherID

Will this type of design where primary keys consist of multiple ID fields from other tables bad from a performance standpoint?

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 Table
--------------------
PK CompanyID


Location Table
--------------------
PK CompanyID
FK LocationID

Teacher Table
--------------------
PK CompanyID
FK LocationID
FK TeacherID

Student Table
--------------------
PK CompanyID
FK LocationID
FK TeacherID

no

what matters more is whether the design is good from a logical standpoint

no portion of a PK may be NULL

the FKs may be NULL, but you need to fix your PKs first

consider your idea here --

Student Table
--------------------
PK CompanyID
FK LocationID
FK TeacherID

this is no good because it says that each student is identified by the company ID, which immediately limits you to one student per company

Good information.