This Question has been Bugging me for Two Years

[viper2843]

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.

[r937]

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

[viper2843]

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.

[r937]

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

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

[viper2843]

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.

[r937]

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

The second rule is ...

not sure i understand the question

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

[foogoo]

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

[viper2843]

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.

[r937]

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

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

[Vali]

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