SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
-
Apr 30, 2008, 15:18 #1
- Join Date
- May 2005
- Posts
- 37
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Database Architecure / Normalization Quandry
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.
-
Apr 30, 2008, 16:37 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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)
-
Apr 30, 2008, 19:19 #3
- Join Date
- May 2005
- Posts
- 37
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Apr 30, 2008, 19:34 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Apr 30, 2008, 20:10 #5
- Join Date
- May 2005
- Posts
- 37
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
That sounds like a interesting plan. I will look into that. Thank you for your time. I appreciate it.
Bookmarks