Thread: History table
09-13-14, 02:28 #1Registered User
- Join Date
- Sep 2014
Unanswered: History table
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?
FieldChanged (Because store number can change if either ownership, franchisee or brand changes)
Or is it better to keep a row intact, so:
Thanks in advance, any advice is appreciated and if my description isn't clear please ask! I'm using a Microsoft SQL server.
09-13-14, 11:42 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
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.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.