Home Forums Database Design Design Ideas and Questions need hlep to decide the apdesign approach RE: need hlep to decide the apdesign approach

  • October 16, 2013 at 4:58 pm

    #1659096

    Abhijit More (10/9/2013)

    1. Data Archival requirement-[/b] as per business I need to archive the data mathcing the buinsess rules. for this I have adopted strategy to create the copy of base transactional table and moving data from base table to archive table.

    ... {snip} ...

    3. Historical data- The requriement says system should capture each modification details of Customer (master+Orders) which I am achieving via AuditLog table but in verticle format. If user generate reports he should be able to see same transactional data at any given time.

    We have reporting requirement where each month the different cusomter reports has been generated by groupin/aggregating data along with Custoemr Details.

    e.g. If user generates Customer details with Sales information report in March 2013 and customer xyz has 100 Orders, system should generate the report with 100 orders.

    but If user genrates Customer Sales information in Oct 2013 for the month of March 2013 (user can select month and year as report parameter). the system should generate only 100 Orders along with same Customer details enough though there is modification happen in between.

    Basically we have to maintain history for each data.

    Thinking about about requirement I came across creating History table same as base tables. But I am not sure whether I am approaching in right direction.

    Appreicate in your opinions on above design apporach or new design apporach.

    Thanks,

    Abhijit More

    Be advised that "reporting requirements" and the "archival" of data creates a huge paradox (especially if you want to see the womb-to-tomb life of rows and still avoid full columnar audits of inserts and deletes) along with some real PITA code. Rather than archiving (it never really happens, either), consider partitioning the data by month to 1 filegroup per month and 1 file perfile group. This will greatly ease backup requirements of huge amount of Audit Data that is never supposed to change and make doing peicemeal restores a possibility.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)