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.