SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by viper2843 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •