Design question - FK column to more than one table

Question

Design question - FK column to more than one table

Viewing 3 posts - 16 through 17 (of 17 total)

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

William Plourde SSCrazy Points: 2035 More actions · Answer 1

opc.three (3/5/2012)
You may be re-inventing the wheel a bit. Depending on how unique your situation is you may be able to leverage some built-in features of SQL Server. Have you looked into Change Data Capture and Change Tracking?
http://msdn.microsoft.com/en-us/library/cc280519.aspx

Um, thanks. I need to read up on that, but it looks like thats a 2008 feature. We are a mixture of 2008 and 2005. So we have to design and code to the lowest common database denominator.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

William Plourde (3/5/2012)
We also have one history table for each base table that we require versioned data on. Our base table(s) generally only have a few Ids with all or most of the data in the corresponding version table for each base table.
This gives us our versioned data. But we ALSO have an audit trail so that when a row changes we store an entry. This way we have a centralized list of data that has changed in the system that we can then use to determine what to send back to the connected systems for updating.

If you want a central spot to see all changes - why not simply use a view?

As in - simply keep the base table history tables as your physical versioned data repository. Any central view could simply be run from a view, consisting of UNION'ed data from each of the base tables (UNION ALL). I didn't hear any specific reasons to go after things like temporal data, so the view might be enough to handle your querying. All while not violating normalization.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

William Plourde (3/5/2012)
opc.three (3/5/2012)
You may be re-inventing the wheel a bit. Depending on how unique your situation is you may be able to leverage some built-in features of SQL Server. Have you looked into Change Data Capture and Change Tracking?
http://msdn.microsoft.com/en-us/library/cc280519.aspx
Um, thanks. I need to read up on that, but it looks like thats a 2008 feature. We are a mixture of 2008 and 2005. So we have to design and code to the lowest common database denominator.

Yes, it is 2008 and above. Bummer. The two types of data you're managing, audit and historical, are both made easily consumable by Change Data Capture (CDC). Is upgrade an option? If you can quantify your effort in implementing this manually versus upgrading it may be an easy sell.

If you must implement this manually then you are smart to ask the community, but I fear there is no easy or clean solution which is probably the itch CDC and CT were meant to scratch. Your history tables are likely fine, as is. For your original question re: audit data, it may not be perfect, but I think a central audit table containing the table's object_id (from sys.tables/objects) plus the singular PK value (provided they are all singular) for the rows that changed will be a solid way forward. It will stay in the background (i.e. no columns on tables that changed) and could theoretically be implemented as a generic trigger added to all tables where you need to audit change.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato