Primary and Foriegn Key using date range

Last Post 30 Jan 2006 04:36 PM by mwesch. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
MelsMax
New Member
New Member

--
30 Jan 2006 07:38 AM
I have a table Client that has the fields ClientID, Name, Address, Phone, etc. Another table with transaction data with the clientid and date of the transaction. I need to be able to do historical reporting. For example if the Client changes thier name I need to be able to run a report from before the name change and have the name appear as it was then and if I run a report from now have the name appear as it is now.

I have added fields to the Client table of EffectStDate and EffectEndDate to give the data an effective range. This works quite well for my reporting. I need to add a primary key to the Client table and have choosen ClientID, EffectStDate, and EffectEndDate. Great now I have my primary key and a really good index.

Now what I would like to be able to do is create a Foriegn Key on the transaction table to the Client table. Is this possible? It does not look like it. I could enforce referential integrity programatically and with constraints but having the relationship would be nice.

I could split the Client table into client and client information but I really need to keep this database in sync with one for another business group that is almost identical but they do not need the historical reporting that we do at this point.

I have a number of other "Master" tables that I will need to do this for.

Will I be able to create the foriegn keys or will I have to settle for just the Primary Keys?
mwesch
New Member
New Member

--
30 Jan 2006 04:36 PM
If you've added StDate and EndDate to Client table, then it sounds like you are storing multiple records in Client table for each client. So then what are you storing in your transaction table? Sounds like you don't need 2 tables?

More typically, you wouldn't change the Client table at all, leaving your PKey as ClientID. Then you would add a transaction table that had all the same fields as Client table, plus a StartDate field. This transaction table would have a PKey of ClientID, StartDate; and a FKey relationship of ClientID mapping to Client table.

Beware of using datetime values as keys or unique constraints. It might work for low transaction systems, but datetime values are only accurate to .003 seconds. If you have the potential of 2 users updating the same record simultaneously, you may end up with unique key constraint errors.
You are not authorized to post a reply.

Acceptable Use Policy