Results 1 to 2 of 2

Thread: History table

  1. #1
    Join Date
    Sep 2014
    Posts
    1

    Unanswered: History table

    Hi,

    I am designing a database for a company that has franchise and company stores. Each store is given a 'store number', which is changed whenever the franchisee changes, or the store changes between company and franchise. A store can also change brand, in which case the store number is also changed.

    I would like to know what is the best way to keep a history of these values. Would a history table with the following fields work?

    UniqueStoreID
    StoreNumber
    FieldChanged (Because store number can change if either ownership, franchisee or brand changes)
    Date
    OldValue
    NewValue

    Or is it better to keep a row intact, so:

    UniqueStoreID
    StoreNumber
    Ownership_ID
    Brand_ID
    Franchisee_ID
    Date

    Thanks in advance, any advice is appreciated and if my description isn't clear please ask! I'm using a Microsoft SQL server.

    Daniel

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Having been in this situation many times before, I'm going to offer a slightly different way to think about the problem that I think you'll find a LOT more useful!

    When you look at store numbers the way you've described, they appear to be a Slowly Changing Dimension, but they really aren't. After you factor in mergers and acquisitions, this problem quickly degenerates to become a Group problem (and the grouping itself might well be a Slowly Changing Dimension too).

    For the simple case of just grouping, all you need is a table to track the StoreNumber and its associated GroupID. If your needs are more sophisticated (like tracking groups over time), then you'll need to make the grouping table appropriately sophisticated.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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