#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    226
    Rep Power
    6

    Scheme where some types of users have a company


    Hi,

    I'm making a database which holds a Users table..

    I have 3 types of users

    • Employees
    • Coaches
    • Admins


    employees and admins have a company. So they should have a company_id field.
    But admins don't.

    Do I add the field to the users table and set it to NULL for admins?
    or do I need more tables in between?

    This is how I'm thinking of doing it.

    dbscheme3.jpg

    Thank you!
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,435
    Rep Power
    9645
    What do you mean "have" a company? Own? Belong to?

    Here's a couple questions that you can answer that will lead to a precise answer:
    1. For a single user, what is the minimum and maximum number of companies they can "have"?
    2. For a single company, what is the minimum and maximum number of users that can "have" them?
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    226
    Rep Power
    6
    Originally Posted by requinix
    What do you mean "have" a company? Own? Belong to?

    Here's a couple questions that you can answer that will lead to a precise answer:
    1. For a single user, what is the minimum and maximum number of companies they can "have"?
    2. For a single company, what is the minimum and maximum number of users that can "have" them?
    Thank you for your time.
    I mean Belong To.

    A user will only belong to 1 company ( if they belong to one ).
    A company will have multiple users
    Admin's won't belong to a Company.
  6. #4
  7. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,435
    Rep Power
    9645
    Then a nullable column in the user table referencing the company would do the job.

    However, do you want to do anything more? Maybe keep a history of users and companies? Join or quit dates?
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    226
    Rep Power
    6
    Originally Posted by requinix
    Then a nullable column in the user table referencing the company would do the job.

    However, do you want to do anything more? Maybe keep a history of users and companies? Join or quit dates?
    Thanks for your reply and your time!. I don't at the moment. What would you advice if I would say yes?
  10. #6
  11. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,435
    Rep Power
    9645
    If you wanted history, or basically wanted more than one relationship (even if only one is "active" at a time), then you would need another table. And if you have another table then you don't need that added column with the user - it does help with performance if you frequently want just the current employment, but it does duplicate some information and would require you to keep both pieces (the column and the separate table) in sync.

    Basically it goes like
    Code:
    /* with company_id column */
    SELECT users.name AS user_name, companies.name AS company_name
    FROM users
    LEFT JOIN companies ON users.company_id = companies.id
    
    /* with history table and no company_id column
    
    user_id | company_id | start_date | end_date
    --------+------------+------------+-----------
        123 |        456 | 2016-01-01 | 2016-11-30
        123 |        789 | 2016-12-01 | NULL
    
    */
    SELECT users.name AS user_name, companies.name AS company_name
    FROM users
    LEFT JOIN employment ON users.id = employment.user_id AND employment.end_date IS NULL
    LEFT JOIN companies ON employment.company_id = companies.id
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    226
    Rep Power
    6
    Originally Posted by requinix
    If you wanted history, or basically wanted more than one relationship (even if only one is "active" at a time), then you would need another table. And if you have another table then you don't need that added column with the user - it does help with performance if you frequently want just the current employment, but it does duplicate some information and would require you to keep both pieces (the column and the separate table) in sync.

    Basically it goes like
    Code:
    /* with company_id column */
    SELECT users.name AS user_name, companies.name AS company_name
    FROM users
    LEFT JOIN companies ON users.company_id = companies.id
    
    /* with history table and no company_id column
    
    user_id | company_id | start_date | end_date
    --------+------------+------------+-----------
        123 |        456 | 2016-01-01 | 2016-11-30
        123 |        789 | 2016-12-01 | NULL
    
    */
    SELECT users.name AS user_name, companies.name AS company_name
    FROM users
    LEFT JOIN employment ON users.id = employment.user_id AND employment.end_date IS NULL
    LEFT JOIN companies ON employment.company_id = companies.id
    The history table looks good! This would also allow me to reuse a user so they can exist in multiple companies, right?

    Thank you for your time!
  14. #8
  15. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,435
    Rep Power
    9645
    Yes: the query would return multiple rows, each with the same user data but with different company data.

IMN logo majestic logo threadwatch logo seochat tools logo