locked
History Tracking Lookup Values RRS feed

  • Question

  • Hi, we need to design a database for the followoing scenario:

    1. Country and Product are lookup tables, with ID and Name columns.

    2. For each unique combination of Country and Product, a Rate value need to be assocaited.

    3. There will be daily update to the Rates values for each combination of Country and Product.

    4. As most of Country and Production combination will have the same Rate value, we are thinking add a Group table in the middle to group the Country and Product combination and map the Group with the Rate to save massive update.

    5. we all need history tracking (point in time) the Rate value for any Country and Product combination at a certain time .

    Is there any industry best pratice for this screnairo? i.e. grouping to save massive update, history tracking using StartDate and EndDate columns?

    Any help will be much appreciated.

    Thanks

    Wednesday, August 24, 2011 4:20 PM

Answers

  • 1. Why use Id for country where the name will suffice?
    4. If there is a default rate, i would add the default rate to the Product TABLE. Then, Country_Product uses the default if Rate is NULL.
    5.

    Country_Product_History
    ---------------------------
    Country
    Product
    Changed (DATE)
    Rate

    I do not know what is consider best practice. There is no need for an End Date as it is derivable from each change's date.

    Wednesday, August 24, 2011 5:05 PM
    Answerer