Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    631
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Advice about normalization and data entry (xp)

    Hi

    I am developing a database to store results of a survey of machinery costs. The heart of the survey will likely be a table structured like this:

    atblRepairCosts
    RepairCostID - PK - autonumber
    OperationIDfk - long - foreign key to an "operations/location" table
    MachineTypeIDfk - long - foreign key to a "machine type" table
    ComponentIDfk - long - foreign key to a "component" table
    YearOfOccurence - long - when in the machine's lifetime did the cost occur (eg, year 1, 2, 3, etc)
    AnnualCost - long - cost supplied by survey participants

    All pretty standard, and it will give me the flexibility I need for analysis and reporting. The problem occurs at data-input time. This will be an interactive survey where the participants should be able to view a datasheet for a selected operation and a selected machine type, consisting of one row for each component type, and one column for each year.

    If I were to denormalize the data, and create fields for Years 1-10, the data entry would be simplified, but the analysis would be more difficult. What is the best mechanism for moving data back and forth between the spreadsheet view which is well-suited for interactive data entry and discussion, and the normalized view which is optimized for analysis? How have other people handled similar situations?

    Any suggestions appreciated.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Advice about normalization and data entry (xp)

    Perhaps you should use a denormalized design during data entry by creating a separate field for each year. It is relatively easy to create a normalized table from this using DAO or ADO code; you can find examples in this forum, or look at the sample Normalize/Denormalize database on Roger's Access Library.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,430
    Thanks
    1
    Thanked 33 Times in 33 Posts

    Re: Advice about normalization and data entry (xp)

    I think you should have separate view for data entry vs. "discussion". The data entry view would be a form that utilizes subforms. The discussion view would be a crosstab query that displays your normalized year information into a tabular format.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    631
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice about normalization and data entry (xp)

    Thanks for the link, Hans. It gave me some ideas. I have gone ahead and built two tables for the data -- one is normalized and one is a "scratchpad" denormalized table. Wrote some code to move the data back and forth between the two tables. The tricky part, not covered in Roger's article, was how to handle items that do not appear in the normalized view (because there are currently no data with those values) yet to have them available as rows in the denormalized view (because they will potentially be populated with data).
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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