#1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,122
    Rep Power
    539

    Does superset table need a column to indicate type?


    Is there any reason to include column -subset_type in the superset table? If so, why? Thank you

    Code:
    people
    -id
    -subset_type (FK to subset.type) 
    -name
    -email
    -address
    -etc
    
    teachers
    -id (PK and FK to people.id)
    -grade_taught
    -etc
    
    students
    -id (PK and FK to people.id)
    -grade
    -etc
    
    parents
    -id (PK and FK to people.id)
    -occupation
    -etc
    
    subtable
    -type (PK. Table includes three records with values "teachers", "students", and "parents")
  2. #2
  3. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,688
    Rep Power
    1842
    What would happen if a parent was also a teacher?
    I suspect you'd want a way of identifying and/or separating the 'people' into distinct types, depending on the application. I'd be inclined to go toward a link table, with a key back to people and another column that would identify the type.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    in my experience (which is somewhat limited) i cannot see what good that column does

    if your query was "list all students that..." you wouldn't need it, you'd just join people to students
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,122
    Rep Power
    539
    Thanks you SimonJM and r937,

    Yea, I too never saw the use. Just recall seeing several articles saying one should do so. I suppose if a critical business rule was a single subset could only belong to a superset, perhaps a unique constraint could be enforced.

IMN logo majestic logo threadwatch logo seochat tools logo