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.:slight_smile:

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/davidportas/archive/2007/01/08/Distributed-Keys-and-Disjoint-Subtypes.aspx

:slight_smile:

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. :lol:

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.

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

LEFT OUTER JOIN to both of them

you can use COALESCE on the returned columns, or a CASE expression for finer tuning

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.

but they should

every table should have a primary key

not sure i understand the question

could you do an update for me on the latest table layout?

Wouldn’t it be easier to just add a column to the devices table that specifies whether the owner is a user or dept?

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.

yes they do – any PK is distinct by definition :wink:

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

I would do this:


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