Referential Integrity Problem

[Vin]

I have transaction table where the rows entered into the transaction
can come a result of changes that take place if four different tables.
So the situation is as follows:

Transaction Table
-TranId
-Calc Amount

Table 1 (the amount is inserted into the transaction table)
- Tb1Id
- Tb1Amt

Table 2 (an amount is calculated based on the percentage and inserted
into the transaction table)
-Tbl2Id
-Tb2Percentage

Table 3 (the amount is inserted into the transaction table)
-Tbl3Id
-Tbl3Amut

Table 4 (an amount is calculated based on the percentage and inserted
into the transaction table. )
-Tbl2Id
-Tb2Percentage

How do I create referential integrity between the Transaction table and
the rest of the tables. When I make changes to the values in Table 1 -
4, I need to be able to reflect this in the Transaction table.

Thanks.

 

[Vin]

Please note that the transaction table could have one or many entries
derived from Table 1 to 4.

 

[Tim Ferguson]

I have transaction table where the rows entered into the transaction
can come a result of changes that take place if four different tables.
So the situation is as follows:

I don't think this is a RI problem, I think it's a RD (relational design)
problem.

If the first table is a table of Transactions, what are the other tables
lists of? What you have described is a list of processes, not entities.
Perhaps what you really need is one table and four queries?

Tim F

 

[Vin]

Well the application we are designing is for Premium calcuation. The
transaction table will store all the premium calcuation breakdowns.
The reference to table 1 - 4 are various elements within the system
that will generate chargeable premiums. Eg. the product selected;
discounts applied to the customer; specific endorsements; Payment
menthod discounts etc. Once the policy is live a live state, changes
to the source
tables (eg product, discount amount) etc or even the cancellation of
the policy will raise NEW records in the transaction tables.

We can manage all this from code directly to ensure all is in sync, but
I prefer to apply referential integrity managed by the DBMS.

 

[KARL DEWEY]

For Referential Integrity the tables must have common data. What you posted
for your table structure does not have common information.

Of the structure you posted there is only one matching element Table4.Tbl2Id
to Table2.Tbl2Id and I believe that is a typo.

 

[Tim Ferguson]

We can manage all this from code directly to ensure all is in sync, but
I prefer to apply referential integrity managed by the DBMS.

Well, yes that is what a database is for but...

transaction table will store all the premium calcuation breakdowns.

No: you keep the breakdowns in a table of Breakdowns

The reference to table 1 - 4 are various elements within the system
that will generate chargeable premiums. Eg. the product selected;
discounts applied to the customer; specific endorsements; Payment
menthod discounts etc.

Okay, these are candidates for tables too, such as Products, Discounts,
Customers, Endorsements, PaymentMethods

Changes to the source tables
(eg product, discount amount) etc or even the cancellation of
the policy will raise NEW records in the transaction tables.

There are lots of ways implementing audit trails -- at simplest you need
a table of Changes but sometimes it has to be a bit more sophisticated.

What you suggested earlier, frankly, does not look like a database
design. I guess it might have come from a successful spreadsheet design,
where you can expect a change in one place to ripple through other
results, but I am afraid that just ain't the way databases work.

You might do well to study the Northwind sample database; not because the
business areas is close to yours, but to get an idea of what goes into a
table, what normalisation means, and what should be left up to the
application.

Hope that helps


Tim F