Referential Integrity - linking multiple tables to transaction table

[heyvinay]

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.

[markc600]

Can you make "Transaction Table" into a view
based on joins between your other 4 tables?

Where does TransID come from?

[heyvinay]

Thanks,

Trans ID is generated - identify column.

We thought of using a view. But the when the core object is set to
LIVE, all changes from then on will be tracked as new transactions in
the transaction table. So in essence, you have have a row in Table 1 -
4 that has multiple transactions in the transacation table. We could
add a column to the Transaction Table set it to refer to the Ids of
Table 1 - 4. But we won't be able to use the DBMS contraints to enfore
this. Possibly a middle table to each Table 1 to 4?

[Erland Sommarskog]

(he******@gmail.com) writes:

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.

Depends on what you mean with changes, but obviously if you change an
amount in one table and you want that to affect the CalcAmount in the
Transaction table, then you should consider a trigger. For that to
work, the Transaction table need to have one FK column per referencing
table.
Your description was quite brief, and very abstract. It is not at all
impossible that there is a better design, if you can give more meat of
what is behind the various tables.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[heyvinay]

Thanks.
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 generated chargeable premiums. Eg. the product selected;
discounts applied to the customer; specific endorsements; Payment
menthod discounts etc. Once policy is live, and 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.

You wrote: "For that to work, the Transaction table need to have one FK
column per referencing table. " - does MS SQL allow null values for
foreign key constraints?

Thanks

[Erland Sommarskog]

(he******@gmail.com) writes:

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.
Referential integrity is about the integrity of - references. That is,
if the Orders table has a FK constraint to table Customers, you cannot
add an Order for a non-existing customer, and you cannot delete a customer
that has an order.

As I understood it, you want one data in one table be the result of data
in other tables. This cannot be achieved with referential integrity. You
can use views, and under some circumstances you can materialise a view.
You wrote: "For that to work, the Transaction table need to have one FK
column per referencing table. " - does MS SQL allow null values for
foreign key constraints?

Yes. For instance, in a Customers table, you may have Citizen column
table that has a foreign-key constraint to the Countries table. But
this column must be nullable, since some people stateless. Not talking
of juridical persons, who normally are not citizens.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Doug]

Off the top of my head, two fundamental ways.

Insert into the 4 tables, and have triggers on those tables for insert,
update, and delete that add rows to the trans table.

Two. utilize standardized stored procedures you use to add/edit/update
the 4 tables. Inside the stored procedures, do a begin trans/ commit
around the changes and the trans table. In this way, you can pretty
much ensure that it will keep up.

The stored procedure has the advantage of allowing more business logic
around changes. You can validate data changes external to the app.
However, you MUST enforce that all changes to the 4 tables MUST uses
the specified stored procedures.

The Triggers have the advantage of "simplicity." Again though,. you are
somewhat limited in what logic you can use.

Two comments you didn't ask for. Be very stingy with indexes on the
trans table. I'm not saying ot have none, BUT don't have a lot of
indexes. And the indexes you do have should be fairly unique. Trans
tables get very large very fast, and they really slow everything down
in a sneaky insidious sort of way in a few years.

Secondly, it can be hard to get the :"right" infomration into the trans
table. As a test to see whether you have all data you need, manually
try to take each trans record, and recreate the final data results. In
a perfect world, your trans table will allow a picture of exactly what
changed when and how, allowing hte final results to be seen.
For a final test, just go ahead and write the program that will read
the trans data and recreate teh table. This is almost never trivial,
but almost always worthwhile.
-doug