SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    This Question has been Bugging me for Two Years

    I've had a database problem that has been bugging me for a couple of years now. Back in college I was working on a project and I implemented a database for something similar and I was never quite satisfied with the results. Here is the criteria I was working with:

    • There are three tables: Users, Departments, and Devices
    • A device must have one and only one owner.
    • A device owner can be a user or a department, but not both.
    • Users and departments don't have anything in common except that they can both own a device.


    Back in the day, I just had two fields that were foreign keys for users and departments and I made sure that only one was filled for each device. It got the job done but I was never happy with the nullable field.

    What I want to know is how to model this better. I was thinking of using some kind of subtype/supertype 1-to-1 relationship but there really isn't any fields in common with users and departments. I have attached a small er diagram to give an idea of what I was thinking.

    Does anyone have a good way of modeling this? Thanks for your help.
    Attached Images Attached Images

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's almost exactly how i would do it

    i would allow the users and departments to have their own PKs, and put owner_id into each of them as FK

    neither your schema nor the one i just mentioned, however, would prevent both a user and a department from referencing the same owner_id

    more magic is required

    see http://consultingblogs.emc.com/david...-Subtypes.aspx

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply. Is there any particular reason why you would have a different primary key for the user's and departments table?

    The distributed key looks like it will work. Its use of check constrains pretty much eliminates MySQL as a compatible database which doesn't bother me a bit.

    I have not used that many supertype/subtype relationships before so I am not sure how to write all my queries. For example, if I had a device id from the devices table, how would I write a query to get the owner details (user info if its a user subtype, or department details if it is a department subtype)?

    Thanks for the help.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by viper2843 View Post
    Thanks for the reply. Is there any particular reason why you would have a different primary key for the user's and departments table?
    not on purpose, and not if i'm building a new system from scratch, but everywhere i've worked, the user and department tables were already in existence with their own ("legacy") keys

    Quote Originally Posted by viper2843 View Post
    ... how would I write a query to get the owner details (user info if its a user subtype, or department details if it is a department subtype)?
    LEFT OUTER JOIN to both of them

    you can use COALESCE on the returned columns, or a CASE expression for finer tuning
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Let me throw a couple of new rules into the mix. The example I gave was missing a couple of things I would like to have. The first is that users belong to departments in a many to many relationship. With a normal many to many relationship I would just create a third table to link the two entities but now that we are adding in this distributed key and the subtypes don't have their own primary keys, I am not sure how to link the two. Is this a reason for adding a different primary key to Users and Departments?

    The second rule is I want to add two fields in the Devices table: one called user_created and the other user_last_updated. These fields would contain the user id that entered the order (on behalf of a department if the device is for a department) and the last user to update the device. If I just kept the owner id for the subtypes I could have the potential of storing a departments owner id. What is the way around this limitation? Thanks.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by viper2843 View Post
    ... but now that we are adding in this distributed key and the subtypes don't have their own primary keys...
    but they should

    every table should have a primary key

    Quote Originally Posted by viper2843 View Post
    The second rule is ...
    not sure i understand the question

    could you do an update for me on the latest table layout?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member foogoo's Avatar
    Join Date
    Mar 2010
    Location
    Vancouver Island, BC, Canada
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wouldn't it be easier to just add a column to the devices table that specifies whether the owner is a user or dept?

  8. #8
    SitePoint Enthusiast
    Join Date
    May 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have attached the latest ER diagram with some of the updates that I was attempting to describe in my last post. My apologies for the confusing post.

    What I meant by the tables not having their own primary keys was they don't have their own distinct key. They share the owner id as their key instead of having a different user id and department id.

    I hope the diagram helps to explain it.
    Attached Images Attached Images

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by viper2843 View Post
    What I meant by the tables not having their own primary keys was they don't have their own distinct key.
    yes they do -- any PK is distinct by definition

    Quote Originally Posted by viper2843 View Post
    They share the owner id as their key instead of having a different user id and department id.
    that's okay

    but the FK1 in both user and department tables must reference either the PK or a unique key, so presumably you've declared the composite(owner_id,owner_type) as unique in the owners table

    let us know what happens when you populate the tables with data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would do this:

    Code:
    Account
    - account_id
    
    Users
    - user_id
    - account_id
    
    Departments
    - department_id
    - account_id
    
    Devices
    - device_id
    - etc
    
    AccountDevices
    - id
    - account_id
    - device_id
    Since your owners have nothing in common, except...


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
  •