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

    Join Date
    Sep 2006
    Posts
    2,122
    Rep Power
    539

    Questions about designing a couple tables


    I have the following (4) tables:

    Code:
    companies
    -id (surrogate PK)
    -organizationsName, organizationsPhonenumber, organizationsAddress, etc 
    -severalColumnsSpecificToThisTableOnly
    
    users
    -id (surrogate PK)
    -companies_id (FK to companies)
    -personsName, personsPhonenumber, personsEmail, etc 
    -severalColumnsSpecificToThisTableOnly
    
    accounts
    -id (surrogate PK)
    -companies_id (FK to companies)
    -organizationsName, organizationsPhonenumber, organizationsAddress, etc 
    -severalColumnsSpecificToThisTableOnly
    
    contacts
    -id (surrogate PK)
    -accounts_id (FK to accounts)
    -personsName, personsPhonenumber, personsEmail, etc 
    -severalColumnsSpecificToThisTableOnly
    I have other tables which have a FK to either companies or accounts. I also have other tables which have a FK to either users or contacts.

    I have other tables which just have a FK to just companies but not accounts. Similarly, I have other tables which just have a FK to just accounts but not companies, users but not contacts, and contacts but not users.

    How would you recommend redesigning these tables?

    For instance, maybe make a super "organizations" table to be used with companies and accounts? Similarly, maybe a super "people" table to be used with users and contacts? The problem I see with this approach is the cases where another table has a FK to just companies but not accounts, etc.

    Or maybe I am thinking about this all wrong.

    Thank you
    Last edited by NotionCommotion; June 13th, 2014 at 06:52 AM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by NotionCommotion
    How would you recommend redesigning these tables?
    why do you feel they need redesigning?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,122
    Rep Power
    539
    Originally Posted by r937
    why do you feel they need redesigning?
    I have another table which has a FK to either companies or accounts. I wish to keep FK integrity. If I just have one FK in the other table, I will need a supertable where companies and accounts use its key as their PK.

    As an alternative, I suppose I could put two FKs in the other table. This will require that I allow NULL in both, however, I always want one or the other (and not both) to have a value.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by NotionCommotion
    I have another table which has a FK to either companies or accounts.
    a single column cannot be a FK to two tables

    did you mean to say that this other table has two FKs, or one? because if it's just one, it can't be a real FK
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,122
    Rep Power
    539
    Originally Posted by r937
    a single column cannot be a FK to two tables

    did you mean to say that this other table has two FKs, or one? because if it's just one, it can't be a real FK
    Let's change "users" to "teachers" and "contacts" to "students".

    Teachers and students both have birth dates, emails, names, etc. I could... put these fields in both the teachers and students table.

    The hypothetical school we are discussing only allows a given student to belong to a given teacher, so I put a FK in students which references the teachers ID. All is good...

    But then I need to keep track of field trips, and I don't really care if students or teachers are part of it. So I create a table called "field_trips" and create.... individual many-to-many tables between students and teachers, and to the field trip table, and use a UNION?

    Then I have another table which assigns a single person (either a teacher or a student) as a representative to some non-profit organization. The many-to-many table references NonProfitTable, but how does it reference either a teacher or a student?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    with a supertype table, often called person

    then student and teacher are subtype tables

    google "supertype/subtype"

    the various FKs will reference the supertype table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,122
    Rep Power
    539
    Originally Posted by r937
    with a supertype table, often called person

    then student and teacher are subtype tables

    google "supertype/subtype"

    the various FKs will reference the supertype table
    Now we are getting somewhere!

    This was my original question. Should I change the schema as described in my original post to a supertype/subtype pattern? Sorry for the confusion, but I forgot they were called supertype/subtype, and just called them "super". It is going to be a lot of work, but if it is the right thing to do, now is the time.


    EDIT. I have decided to make these changes. I will have about 1000 lines of code to change, but I think/hope it is worth it. I do have some questions about enforcing rules when using a superset/subset pattern, and I posted them in the PHP forum. I would appreciate you taking a look Using PHP to enforce database rules and would value your advice. Thank you
    Last edited by NotionCommotion; June 14th, 2014 at 10:07 AM.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by NotionCommotion
    I would appreciate you taking a look Using PHP to enforce database rules and would value your advice. Thank you
    i would move the columns to the subtype tables if they cannot be treated identically in the supertype table

    but that's just me, and i'm sure all the php guys are going to tell you to do it with php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,122
    Rep Power
    539
    Originally Posted by r937
    but that's just me, and i'm sure all the php guys are going to tell you to do it with php
    Yeah, probably so, but I haven't heard from any of them.

    I am happy I am changing to a superset/subset structure even though it will take me the weekend to do so. Strange the things I take pleasure in

IMN logo majestic logo threadwatch logo seochat tools logo