July 29, 2010 at 9:08 pm
I'm not new to SQL, but I don't consider myself an expert yet either. Most of the other people I work with are much more experienced than I am, but I don't always take verbatim what they tell me just because they've been doing it longer.
Today I was discussing with one of the database architects the design of a history table. Say we have a table that holds a row with the current state of something, like an employee for example. His approach is to store the current row in the employee table. In the history table, he would also have the current row and all the historical rows as well.
That seems to me like it isn't exactly a normalized structure. In fact, it seems a bit ridiculous to have the same row in two tables. I think the current row should be maintained in the employee table and ONLY historical rows should be maintained in the employee_history table.
When I challenged the architect, he got a bit defensive and gave me the line that I absolutely hate - "Well, that's the way we've always done it here." I said, "Well, that may be the case, but it doesn't necessarily make it right." He then stated that this design is an "industry standard."
Bottom line - I'll probably wind up doing it his way because that's just the way it goes where I work. I'd just like to hear other opinions to see if I'm on the right track with my thoughts or if his design has merit.
Thanks!
July 30, 2010 at 11:00 am
I can see how both cases can be viable, but if I could pick only one, having the current record in both tables isn't a bad thing. Off the top of my head, if the active record is corrupted, you have an immediate backup to go to -- no need to dig through lots of records to restore it, and, unlike archival records, it would be up to date.
There are probably other reasons as well, but that's what pops into my head right off the bat.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
July 30, 2010 at 12:56 pm
I prefer to store the current in a live table and the history table. This way you don't have to join the live table for just 1 entry, which can be heavy if there are a lot of entries.
Transactional applications use the live table, reporting and such use the history table.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply