Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Primary Key, Normalizing?

    I am a beginner, so please bare with me. I get very confused on how to normalize my database.

    Firstly: The employees in the company I work for are in various departments and can have more then one title and work in more then one department.

    Example: John Smith can work in the engineering department as a detailer and an engineer and at the same time work as a project manager for the management department.

    How do I setup this table structure?

    Code:
    Employees Table
    Login (PK) | First  | Last    | Extension.......
    ---------------------------------------------
    jsmith       | John | Smith | 280
    
    Department Title Breakdown
    Department   |   Title
    --------------------------
    Engineering   |  Detailer
    Engineering   |  Engineer
    Management  | ProjectManager
    
    Job Description 
    Login  |  Title
    -------------------------
    jsmith | Engineer
    jsmith | Detailer
    jsmith | ProjectManager
    This is important to break this down because for each project the following is saved:

    Code:
    Project Listing
    Project | Detailer | Estimator | Sales   | Engineer |....... | Location
    10001  | jsmith    | jdoe        | mslick  | sjunk     | ......  | Las Vegas
    Or should the project be broken down as well
    Code:
    Project Listing
    Project | Location
    10001  | Las Vegas
    
    Project Team
    Project | Member   | Activity
    10001  | jsmith      | Engineer
    10001  | mstevens | Detailer
    Any thoughts on how to normalize this?

    Mike B

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Primary Key, Normalizing?

    MikeB,
    As far as I can tell you have a decent start on the process and relationships. To me, the key determinant of whether you proceed to separate out the various "step owners" for a particular project is how stable the steps to your projects are. If you have always had an Estimator, a Detailer, an Engineer and a Salesperson for every project and every project has just one person doing that particular task, then it's only extra work to break the relationship out into a separate table.

    BUT, if ever there has been a situation where more than one person has done the Estimating (for example) for a project, or if you think you might be adding roles for Quality Assurance and Documentation Specialist in the near future, then it's worth the time to break the relationship out into a separate table.

    Look at your business and try to foresee what things might be like six months from now. If in your mind's eye things aren't going to change, then avoid the extra work and invest it somewhere else.

    But that's just my opinion (and I'm lazy).

    Regards,

    hmscott

    Edit note: Apparently, I'm so lazy that adding an "n't" is just too much work. Sigh.
    Last edited by hmscott; 02-26-04 at 15:02.

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565

    Normalize this??

    when you say 'Normalize', what do you mean?

    Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. I used to get argued about this one quite a bit,, it actually means 1NF)

    How far do you want to go?

    BoyceCoddNF or 3NF or higher

    the true trick to normalization is to consider the pimary key as the parent within the table iteself in a relationship with the other attributes(cols)
    if a col has a many to one relationship to the key, and it isnt a shared dependency with another column in the table. then you are on the right track to 3NF and BCNF

  4. #4
    Join Date
    Feb 2004
    Posts
    134
    [quote]
    and it isnt a shared dependency with another column
    [quote]

    What is meant by a shared dependency?

    Let's say I have a Company Table
    Code:
    Company
    ID (PK) | Name | Address | City | State | .....
    The City and State would be repeated many times, therefore, not dependent and can be many keys to one city / state. This is what you mean correct?

    Should be broken yet even further.
    Code:
    Company
    ID (PK) | Name | Address | Postal (FK)
    
    Zip
    Postal (PK) | City | State
    Mike B

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565

    Functional Dependecy

    i dont know what i was thinking
    i meant functional dependency

    this is where one item is dependent on another for its definition.
    the dependency must hold true for every possible value of the item
    currently and in the future
    ++++++++++++++++++++++++++++++
    Product , Price , Weight
    rock, 1.00, 1#
    rock, 1.00, 1.5#
    Rock, 1.00, .75#
    brick, .75, 2.0#
    brick, .75, 2.3#
    stone, .85,.25#
    marble, 45., 15.#
    ++++++++++++++++++++++++++++++
    price for brick is always .75 even if the weight changes.
    price is functionaly dependent on product
    reperesented as (Product) -> Price
    and actually product is fd on price because each individual product has only one price.
    so that would be (Price) -> Product

    if the price of rocks went up based on the weight (example,,, all rocks that weigh 1.5# will now cost $1.50

    now price is no longer functionally dependent on product alone now the FD is represented as
    (Product, Weight) -> Price

    this is something that you should be aware of and consider highly while designing your tables but remember that the relational rules are good ones but are in no way a law "more of a guideline than anything"

  6. #6
    Join Date
    Feb 2004
    Posts
    134

    Re: Primary Key, Normalizing?

    Originally posted by hmscott
    or if you think you might be adding roles for Quality Assurance and Documentation Specialist in the near future, then it's worth the time to break the relationship out into a separate table.
    Very very possible. Thanks for you comments.

    Mike B

  7. #7
    Join Date
    Feb 2004
    Posts
    134

    Re: Functional Dependecy

    Originally posted by Ruprect
    i dont know what i was thinking
    i meant functional dependency
    Thank you for your comments. I am looking at this, and to tell you the truth, I cannot see one area where this is a problem. Time to take out the magnifying glass and get a closer look

    Mike B

Posting Permissions

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