SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Design Advise

  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2010
    Location
    Cape Town, South Africa
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Design Advise

    Hi,

    So i am working a project which have up to 3 different user types, there are about 30 fields which are exactly the same between all 3 users, then an additional 20 fields belonging to only 2 of the 3 user types(admin and player). And finally there are another 11 fields specific to only the one user type.(player)

    All 3 user type have different uses in the system. One is a admin, one a user and the another a palyer in the system.

    Each user's role in the application is different. And each user will hook up to different tables dependent on their type.

    My question is thus, should I duplicate the table structures into separate tables for each user and then extend each table separately or should I keep a general user table and extend all user type from that table with nullable foreign keys, determining the joining table by the user type.

    Please advise.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jaco.nel007 View Post
    My question is thus, should I duplicate the table structures into separate tables for each user and then extend each table separately or should I keep a general user table and extend all user type from that table with nullable foreign keys, determining the joining table by the user type.
    the answer is somewhere between those two extremes

    do a search for supertype/subtype

    you'll want a main users table, which contains all common columns, and then separate subtype tables for each different type of user, containing only those columns unique to each type

    the subtytpe tables will use the supertype's PK as their own PK but have it as FK as well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2010
    Location
    Cape Town, South Africa
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the subtytpe tables will use the supertype's PK as their own PK but have it as FK as well
    Please correct me if I am wrong, you are suggesting I create a user table to keep all fields relevant to all 3 user types and then create a table for each user type. The tables per user type, subtypes, will then reference the user table, supertype, with a one-to-one relation?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yes, that's the idea
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2010
    Location
    Cape Town, South Africa
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thus any queries for each user specific type will be done vie the subtype tables.

    That will work perfectly! Thank you very much.


Bookmarks

Posting Permissions

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