Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2009
    Posts
    18

    Unanswered: Database Design Question

    Hi,

    I am deconstructing a poorly designed MS Access database and re-creating it in a SQL Server 2005 database. I am trying to normalize and I am having problems.

    So the database will be responsible for tracking IT employee time entry and I have to maintain a history which is what makes it tricky for me b.c employees names can change, change departments, etc.

    The MS Access database's most important table has a lot of repetitive data. The fields are:

    1)Year
    2)Month
    3)Week
    4)ProgramCode - has multiple projects under one program
    5)ProjectCode - rolls up to 1 ProgramCode

    6)UserResponsibilityCode - their department which for offshore resources can change a couple times per year, also out of the 2 thousand employees, at least 10 to 50 change departments, etc.

    7)ResourceName - Employee name

    8)UserIDs - username for logging into computers. ResourceName and UserID are not 100% 1 to 1 b.c of the history. People getting married, leaving and returning, Offshore to Onshore, etc.

    9)ResourceType - Onshore, Offshore, TRV, OTHER and NoCharge

    10)Chargeable - true/false = bit datatype

    11)ResourceSource - Where they are from. The ResourceType field is derived from this field in an update script. ResourceType is a 1 to many for this field There are about 11 or 12 values

    12)ResourceRole - relates to ResourceSource. ResourceSource is a 1 to many for this field.

    13)Activity - description of what the resource is doing. ResourceRole is a 1 to many for this field

    14)CompensationCode - Relates to employee compensation and about 25 to 30 different codes

    15)ProjectCode Description - should be 1 to 1 with the ProjectCode

    I'm not looking for anyone to design it for me but I am looking for ideas.

    Thanks in advance.

    Paul

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You haven't asked anything specific.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    So why do you believe Access database was designed poorly?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Aug 2009
    Posts
    18

    Database Normalization for Historical Data

    I guess my question is what are the best ways to create a normalized database where the you have to keep historical data? Please refere to my prior post for the fields that should probably be broken into different tables.

    I'm looking for ideas on how to do this...

    Thanks for your time.

    Paul

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmm...Well, you have a few fields that need to be captured, but you're also missing some, like activity status, meaning if the activity applied changed the status of the project, etc. Also, what type of activity was it, - proactive, reactive, or N/A...This is just by looking at many available packages and management questions that I usually get. Do you feel that this Acces db has everything you need to move forward?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Aug 2009
    Posts
    18

    Database Design

    Rdjabarov, thank you for responding.

    Yes, I am confident all of the data I need resides in the MS Access database to move forward.

    The part of normalization that I get is making individual tables where there are easy 1 to many relations with another table.

    For example, the ProgramCode to ProjectCodes is 1 to many and I can easily have distinct values in each table with a foreign key in the ProjectCodes that would be the ProgramCodeID.

    Hopefully this helps.

    Thanks,
    Paul

  7. #7
    Join Date
    Aug 2009
    Posts
    18

    Lastly

    What is the best way to load in the Standard Hours, Overtime Hours and Cost fields which are obviously numeric?

    That is the issue that is troubling me the most. I am going to have an ETL package load the flat file the data comes in and I split the file's data into different tables. The part that I can't get my head around is if I am going to split out all the columns into different tables, how do I keep track of numerics (Std Hours, Ot Hours and Cost)

    They need to be loaded into a table and I need a way of keeping track of what hours and cost are associated with which employees, which projects they are working on, which department they are working from...

    AND, I have to keep historical data so an Employee maybe offshore for the first half of the year but onshore for the second half of the year. They may change departments, UserIDs sometimes change....

    Even by writing this stuff I am getting ideas but I would appreciate any help you could provide.

    Thanks,
    Paul

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I have trouble seeing relationships with written text.

    Can you attach a graphical representation of the tables and their relationships?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ease2002
    I guess my question is what are the best ways to create a normalized database where the you have to keep historical data?
    Create your normalized database without consideration of historical data, simply modeling the entities and their attributes. Then add archive tables to record historical data for all the tables where you need to track changes, and use triggers on your primary tables to maintain them.
    Here is a script you can use to generate the archive tables and triggers:
    sqlblindman private pastebin - collaborative debugging tool
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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