Database Architecure / Normalization Quandry

[viper2843]

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.

[r937]

the requirement to make it compatible across database systems means that you will have to write application code

even if you went the route of trying to do it with INSERT and UPDATE triggers, i would not like to write those triggers in the sixteen different dialects of SQL that might be needed

you want portability? you have to sacrifice efficiency (a trigger would be ~way~ faster than application code)

[viper2843]

Well the application will have a database abstraction layer to communicate with the various DBMS systems so I am not worried about that. The thought of triggers has crossed my mind but I really wanted to find some sort of relational solution to the problem. Some way of adding tables to create a bunch of relations to model the situation. Is there another way besides triggers or am I just dreaming. Thanks.

[r937]

Well the application will have a database abstraction layer to communicate with the various DBMS systems so I am not worried about that.

okay, that sounds promising

why not get the abstraction layer to use a CHECK constraint in those databases that support CHECK constraints, and do a series of IFs with SELECTs to accomplish the same thing in databases that don't

[viper2843]

That sounds like a interesting plan. I will look into that. Thank you for your time. I appreciate it.