Dependency Cycle in Database with Update Approvals

Question

Dependency Cycle in Database with Update Approvals

ahlembreath

SSC Rookie

Points: 35
More actions
April 15, 2024 at 9:51 am

#4411347

I'm struggling with a dependency cycle in my SQL database schema. Here's the situation:

I have tables for documents , transactions, payment_methods, and updated_transactions.
Transactions link documents and payment methods, and their state can be updated.
The updated_transactions table stores proposed updates to transactions, referencing both the original transaction and the proposed updated document and payment method IDs.
The problem is that the foreign key relationships create a cycle:
documents > transactions > updated_transactions
documents >updated_transactions
and in
payments_methods > transactions > updated transactions
payments_methods >updated_transactions

Viewing 4 posts - 1 through 3 (of 3 total)

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

Emperor100 SSCrazy Points: 2173 More actions · Answer 1

What type of application are you trying to support , OLTP or OLAP?

If OLTP, combine both transactions and updated_transactions table into one. Add a new table TransactionType which differentiates whether a transaction is original or updated one. Add this TransactionType attribute to the combined new table.

If OLAP, combine the tables transactions and updated_transactions into one and follow SCD Type 2 rules, this design helps in finding which transaction row is active. There are other forms of SCT types too.

Hope that helps.

=======================================================================

ahlembreath SSC Rookie Points: 35 More actions · Answer 2

Thanks for your response and suggestion! I appreciate your help with the dependency cycle issue.

"updated_transactions" is a temporary table to store changes in information before it is actually changed in the original "transactions" table. To clarify, users submit transaction updates, but these updates require administrator approval before being implemented.

ahlembreath SSC Rookie Points: 35 More actions · Answer 3

Thanks for your response and suggestion! I appreciate your help with the dependency cycle issue.

"updated_transactions" is a temporary table to store changes in information before it is actually changed in the original "transactions" table. To clarify, users submit transaction updates, but these updates require administrator approval before being implemented.