I am designing a database (and ER diagram) for a new project I am working on and I can't seem to figure out how to properly normalize one particular component. Here's the logic in my head:
- There are three different types of data, or tables, I am working with: users, departments, and devices.
- Users can blong to departments.
- Users have devices.
- Departments have devices.
- The same device cannot belong to a user and a department at the same time.
- A device must belong to either a user or a department.
The only way I can see of normalizing this correctly is to place foreign keys in the device table for userID and deparmentID and then use a table check constraint to make sure that both fields are never filed in at the same time and that at least one field is filled in. Table check constraints will work great in real DBMS systems like PostgreSQL but I wanted to try to make this cross DB compatable and certain other crappy DBMS systems *cough* MySQL *cough* don't seem to support table check constraints . Is there another approach to solving my problem. Thanks.