Primary Key on Date Range and Foreign Key
-
I have a Client table with data about the Client: name, address, etc. There is also a transaction table with data about the actions performed. The Client table has Fields of ClientId, EffectStDate, EffectEndDate, Name, and Address. The transaction table has the ClientId, transaction details and the date/time of the transaction. This data is comming from another system so I am stuck with the clientid as it is. This structure works fine for my historical reporting. I join to client on clientid where transaction date is between EffectStDate and EffectEndDate and I get the client information as it was at that point. My problem is I am trying to add refferencial integrity and set primary keys and foreign keys.
The data source has a primary key on ClientId in the Client table but does not have the historical data, which I need. So on the Client table I need to make my Primary Key be at least ClientID and EffectStDate. The problem I have is how would I then create a Foreign key on the transaction detail table to client. I want to enforce not being able to store data in transactions that do not have a matching ClientID. I could do this with Check Constraints/Triggers but would like to be able to create relationships.
Am I out of luck? Either way I do want the primary key on Client.
Thanks
-
If you don`t already have a table with a distinct ClientID then you really should have one (presumably with the Name and Address which would also normally be unique for a single client), then both your tables (one would be ClientId, EffectStDate, EffectEndDate, the other ClientId, transaction details, date/time) would have a foreign key on to ClientID in that table.
Otherwise it would indicate that you have not constructed your tables in the most efficient way. For instance, in your current client table, you can have many different clientID and EffectStDate combinations, but all will have the same name and address, which is wasteful of space.
"Don`t try to engage my enthusiasm, I don`t have one."(Marvin)
-
My point is that I could have the same client in the table multiple times with different client names, addresses, phone numbers, etc and that information is valid for a specific date range. I need to be able to link back to the client name at the time the transaction took place. All of my "master" tables will need this functionality. This is needed for historical reporting. For example the customer merged with another company and now has a new name but needs a report from before the merger. The report must show the name of the client as it was at that time not as it is now.
I could split the table into client and client information but the kicker is we are trying to keep this database simular to another one for another business group that does not have the historical reporting needs we do (they will just update the information where we will create another record), but might in the future.
Creating the keys as I outlined works well but we will not be able to have foriegn keys in the other tables. This may not be that big of a deal but I would like to be able to enforce refferencial integrity without having to do it programatically.
-
OK, that makes sense, I work in the financial sector and company names are always changing
Even without splitting the table I would still keep a table with just the unique client IDs (you could maintain certain other useful data here, such as 'Original Name', 'Current Name', 'DateAdded', 'LastChange' etc).
Otherwise you would be looking at a trigger with some 'WHERE TransactionDate BETWEEN EffectStDate AND EffectEndDate' code or whatever. If the transaction table is hit allot you may find this is less efficient than a simple distinct clientId table and a foreign key.
"Don`t try to engage my enthusiasm, I don`t have one."(Marvin)
-
Thanks,
That was what I was thinking was going to have to be done.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply