Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Posts
    67

    best normalized data

    I am designing a database from scratch and have a chance to do it right from the start. I want to get your suggestions or recommendations on some of my thoughts below. So you know, if it’s not obvious I am NOT a DBA by any standard. I'll try to give you the short version to make it as pain free as possible...

    I have to design a database that contains employee and asset data. The employee data will contain information about all employees in the company. The asset data is all servers, laptops, phones and software some used by employees some stationed in server complexes in various parts of the country. This is where my first question comes in. Since both server and employee data have addresses and some assets will be at employee homes I thought a good way to handle it would be to have one table of locations that employee and server can point to. But I can't seem to come up with a nice way to handle it. Since I'd have to create a location table with two keys, one that ties to employee and another that ties to asset data. So when it's an employee location the asset key would be empty and vice versa.

    Field and table names are just for this example....

    table name "employees" fields:
    uid <-- Pkey
    empno
    firstname
    lastname
    addr1
    addr2
    city
    state
    zip
    email
    phone

    table name "assets"
    deviceid <--Pkey
    make
    mod
    mfg
    sn

    Sooo should I create a location table that both the employee and asset table can hit? If so how? Any other suggestions?

    My last question is: I heard recently and am now convinced that you should not use an ID created by the business as a primary key. For instance if I have employee numbers that the business setup to reflect the state of the employee then a number (FL12345) and the employee moves I would have to change my primary keys for the record.

    So if I am not going to use the businesses logic to create my primary key. What's the best way to come up with another one? Should I create an identy field? What's the best way to handle this (pass it around etc).

    Thanks in advance for your help!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    go ahead and use an identity field as the primary key

    never display this identity field, but it's okay to display the company field (FL12345), just like you would display employee name, etc.

    as for the assignment, there are a bunch of ways you can do it, each way has pros and cons

    the way i would do it is not with a third location table, but with foreign keys right in the asset table

    so, an individual asset is located either at an employee's home (foreign key to employee table) or hierarchically inside another asset (foreign key to same asset table), for example win2kpro license #123 is located in server name pluto, win2kpro license #456 located at Joe FCO's home

    it isn't a many-to-many relationship (because an asset can be in only one place at a time) so the foreign keys can live inside the asset table

    the only trick is you have to check two foreign keys to find out where each asset is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Posts
    67

    One question...

    I'm not sure I understand....

    "so, an individual asset is located either at an employee's home (foreign key to employee table) or hierarchically inside another asset (foreign key to same asset table), for example win2kpro license #123 is located in server name pluto, win2kpro license #456 located at Joe FCO's home"

    Is this a correct representation of the above...

    <<< table: employees >>>
    uid locid empno fname lname addr1 city...
    1 11 S1234 Bill Smith 12 Dogwood somecity
    2 5 S9876 Judy Boyce 34 Rockway someothercity
    3 3 S5555 Dave Ward 2 Rosetree anothercity
    4 4 F7777 Tom Apple 5 boxes ave andanotherone


    <<< table: assets >>>
    aid locid mfg desc addr1 city...
    1 10 Dell 2400 Server 9 Shoreline somewherelse
    2 11 Sun 1000E Server -----------------------------------
    3 11 Nokia 43a Cell Ph -----------------------------------
    4 9 Compaq Modem 7 Philly ave andanotherone


    If so how do I maintain the locid field? I would need to make sure that a number I used in one table is not repeated in the other. Is this still good programming practice or normalized to have the address is two tables like this?

    Thanks I appreciate your input.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i think the locationid is throwing you off

    take that out of the employee table

    now have an employee id foreign key in the asset table, to relate an asset to the employee that has it

    after that's done, the only thing that you haven't got is the location of assets that aren't in an employee's home

    now the tricky part, some assets are located "inside" other assets (software loaded on a server)

    that's why the recursive relationship from asset to asset

    the asset table has address columns itself, but only the assets at the top of their hierarchy would have values, e.g. the servers, not their component parts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2003
    Posts
    67
    Think I got now. Is this a good representation?

    Code:
    <<< table: employees >>>
    uid empno fname lname addr1 city...
    1   S1234 Bill Smith 12 Dogwood somecity
    2   S9876 Judy Boyce 34 Rockway someothercity
    3   S5555 Dave Ward 2 Rosetree anothercity
    4   F7777 Tom Apple 5 boxes ave andanotherone
    
    <<< table: assets >>>
    aid uid      mfg            desc        addr1             city...
    1   NULL    Dell 2400    Server     9 Shoreline    somewherelse
    2   1         Sun 1000E  Server     -------------     -------------
    3   4         Nokia 43a   Cell Ph     -------------     -------------
    4   NULL    Compaq     Modem     7 Philly ave    andanotherone
    
    <<< table childassets >>>
    aid    mfg    license    desc        
    2     Sun     1XE435     Solars
    2     Sun     2Z556      J2EE		
    1     MS      XDR55	 Win2000 Adv Serv
    Ok this is what I think will have to happen:
    1.) uid is NULL where the asset is not located at an employees home.
    2.) I can always get employee address but will have to check if the employee has assets in both asset tables using uid and aid.
    3.) If I want to get the address of an asset I will first have to check the asset table for uid if it's NULL take the address if not NULL check employee table.
    4.) When an employee takes an asset home I will have to delete the address from teh asset table and add the uid.

    Hey this doesn't seem so bad.

    So I guess it's still normalized data?

    Thanks for your help!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    looks better except for the assets and childassets, i would combine them

    have you ever seen a recursive relationship for, say, categories? or who-reports-to-whom in an employee table? or a bill of materials parts explosion? this latter is what i meant
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2003
    Posts
    67
    Originally posted by r937
    looks better except for the assets and childassets, i would combine them

    have you ever seen a recursive relationship for, say, categories? or who-reports-to-whom in an employee table? or a bill of materials parts explosion? this latter is what i meant
    Oh yes a few years ago I did exactly that for "who-reports-to-whom". I just remember running into an issue with how to run the query. I ended up writing a query with about 15 levels of sub querys. Problem with this was if the company added more layers of management. I thought there should be a tighter way to write the query but never returned to do it.

    Here is the latest...
    Code:
    <<< table: employees >>>
    uid empno    fname     lname          addr1               city...
    1   S1234      Bill         Smith           12 Dogwood     somecity
    2   S9876      Judy       Boyce          34 Rockway      someothercity
    3   S5555      Dave      Ward            2 Rosetree       anothercity
    4   F7777       Tom       Apple           5 boxes ave      andanotherone
    
    <<< table: assets >>>
    aid uid      cid      mfg               desc               addr1             city...
    1   4         NULL     Dell 2400     Server        9 Shoreline    somewherelse
    2   1         NULL     Sun 1000E   Server       -------------     -------------
    3   4         NULL     Nokia 43a    Cell Ph       -------------     -------------
    4   NULL    NULL     Compaq      Modem      7 Philly ave    andanotherone
    5   NULL    2        Sun         Solars
    6   NULL    2        Sun         J2EE		
    7   4         1        MS          Win2000 Adv Serv

    Thanks for your help.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're welcome

    thanks for catching on so quickly to what i meant

    mine isn't the only design solution, or necessarily the best, it's just the way i'd do it

    good luck with the queries

    there is no nice way to do recursion in sql (except oracle can do it with a proprietary sql extension)

    but luckily in this example your parts explosion will likely go down no more than two levels
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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