Table Design for Real World App

Question

Table Design for Real World App

TBIG

SSC-Addicted

Points: 479
More actions
December 6, 2007 at 10:00 am

#95547

I need some DBA expertise.
We have developed a web app for interior design specifications. Each specification has a Manufacturer associated with it (joined on the PK of the Manufacturer data table).
My clients want to preserve the Manufacturer data for the specification. For example, when a specification is a year or two old and the Manufacturer data has been changed, they want to see the orginal data.
Should I just capture the data for the Manufacturer data when it is selected for a specification and keep it in a separate data table?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Loner SSC-Insane Points: 21279 More actions · Answer 1

When I design a data warehouse, what you explained we call it slowly change dimension.

When I create the table, there primary key is a surrogate key - usually identity field instead of the natural id. I also have a field called 'Current' or 'Status' - it is up to you and the creation date. You can put an index on the natural id.

For example, it is a customer table, the PK is cust_key which is an identity key, customer_id is the real id. If the data is changed, I inserted a new record in the table with a new cust_key but with the same customer_id and the new data. The status field of the new record becomes 'C' - current and the status of the old record becomes blank or 'O' - old, it is up to you.

This way you can view all records of the customer in the order of the creation date or just view the customer record with status = 'C'. Of course if any other table has a foregin key to the customer table, you need to change to point the current record.

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 2

TBIG,

What you're describing is an "Audit Process" or "Audit Trail"... there are many ways to do this including Audit Triggers that save "old data" to an Audit table when data in the original table is introduced.

--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)

Steve Jones - SSC Editor SSC Guru Points: 736059 More actions · Answer 3

It really is a slowly changing dimension, but in an OLTP system, I think it fits more as an auditing item as Jeff mentioned.

I'd store it separately as it's entered and leave that as the item of record. Then you can have a more recent activity copy that gets changed as things are changed in the business. It gets complicated either way because at some point you might be editing both copies, and at some point you want to stamp the audit record as the final source and not let it be changed. Might want to use some flag there that prevents changes (even perhaps a trigger that rolls things back if the flag is set).