Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    1

    Help with e-Learning Database Design

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Fulham
    Will this type of design where primary keys consist of multiple ID fields from other tables bad from a performance standpoint?
    no

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

    Quote Originally Posted by Fulham
    In the standalone student scenario the student table entry would have NULLS in the ComapnyID, LocationID and TeacherID.
    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
    Last edited by r937; 06-22-09 at 17:12.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Posts
    7
    Good information.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •