Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Smile Help creating a simple WebLogin Relation

    Hey,

    I have a preexisting database with tables Teacher, Principal, AssistantPrincipal. I want to create a WebLogin Entity (WebLoginId, Username, Password) that shares a one to one relationship with each of the tables above. For every teacher, there is exactly one corresponding WebLogin. For every WebLogin, there is exactly one corresponding teacher.

    My first thought is to create a relation such as WebLogin(WebLoginId, Username, Password, TeacherId, PrincipalId, AssistantPrincipalId).
    The TeacherId, PrincipalId, and AssistantPrincipalId would be foreign keys into their respective tables. However, in this method, two of the employee IDs are always left null.

    Is there a more elegant solution? I'm quite new at this.

    Thanks!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Given your existing data model, there probably isn't. Your solution is quite a standard one anyway - with a CHECK constraint to ensure that one and only one of TeacherId, PrincipalId, and AssistantPrincipalId is populated on each row.

    You could turn it around and hold a WebLoginId in the other 3 tables, but then it would be hard (not impossible) to ensure that a Teacher and a Principal (say) don't share the same login details.

    Maybe Teachers, Principals and Assistant Principals should all have been in one table in the first place, but it's too late to worry about that now.

  3. #3
    Join Date
    Apr 2009
    Posts
    4
    Thank you for the response Tony.

    Is it a better practice to split the table into several smaller linking tables to eliminate the nulls?

    WebLogin(WebLoginId, Username, Password)
    WebLoginTeacher(WebLoginId, TeacherId)
    WebLoginPrincipal(WebLoginId, PrincipalId)

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I don't think so, no. It has the same issues as putting foreign keys to WebLogin in the other 3 tables, i.e. hard to ensure a Teacher and a Principal don't share the same WebLogin. Plus it involves extra tables.

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Looks like a supertype / subtype example to me:

    WebLogin (TeacherId, WebLoginId, Username, Password)
    KEY (TeacherId)
    KEY (WebLoginId)

    Teacher (TeacherId) KEY (TeacherId)
    Principal (TeacherId) KEY (TeacherId)
    AssistantPrincipal (TeacherId) KEY (TeacherId)


    Teacher is the parent table being referenced by all the others.

Posting Permissions

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