Question Unprotected by Paul White says GoFundMonica
5 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link
  1. Have a separate balances table and do one of the following:

    1. Apply transactions to both the transactions and balances tables. Use TRANSACTION logic in my stored procedure layer to ensure that balances and transactions are always in sync. (Supported by JackJack.)

    2. Apply transactions to the transactions table and have a trigger that updates the balances table for me with the transaction amount.

    3. Apply transactions to the balances table and have a trigger that adds a new entry in the transactions table for me with the transaction amount.

    I have to rely on security-based approaches to make sure no changes can be made outside of the stored procedures. Otherwise, for example, some process could directly insert a transaction into the transactions table and under scheme 1.3 the relevant balance would be out of sync.

  2. Have a balances indexed view that aggregates the transactions appropriately. Balances are guaranteed by the storage engine to stay in sync with their transactions, so I don't need to rely on security-based approaches to guarantee this. On the other hand, I cannot enforce balances be non-negative anymore since views -- even indexed views -- cannot have CHECK constraints. (Supported by DennyDenny.)

  3. Have just a transactions table but with an additional column to store the balance effective right after that transaction executed. Thus, the latest transaction record for a user and currency also contains their current balance. (Suggested below by AndrewAndrew; variant proposed by garikgarik.)

When I first tackled this problem, I read thesethese twotwo discussions and decided on option 2. For reference, you can see a bare-bones implementation of it here.

  1. Have a separate balances table and do one of the following:

    1. Apply transactions to both the transactions and balances tables. Use TRANSACTION logic in my stored procedure layer to ensure that balances and transactions are always in sync. (Supported by Jack.)

    2. Apply transactions to the transactions table and have a trigger that updates the balances table for me with the transaction amount.

    3. Apply transactions to the balances table and have a trigger that adds a new entry in the transactions table for me with the transaction amount.

    I have to rely on security-based approaches to make sure no changes can be made outside of the stored procedures. Otherwise, for example, some process could directly insert a transaction into the transactions table and under scheme 1.3 the relevant balance would be out of sync.

  2. Have a balances indexed view that aggregates the transactions appropriately. Balances are guaranteed by the storage engine to stay in sync with their transactions, so I don't need to rely on security-based approaches to guarantee this. On the other hand, I cannot enforce balances be non-negative anymore since views -- even indexed views -- cannot have CHECK constraints. (Supported by Denny.)

  3. Have just a transactions table but with an additional column to store the balance effective right after that transaction executed. Thus, the latest transaction record for a user and currency also contains their current balance. (Suggested below by Andrew; variant proposed by garik.)

When I first tackled this problem, I read these two discussions and decided on option 2. For reference, you can see a bare-bones implementation of it here.

  1. Have a separate balances table and do one of the following:

    1. Apply transactions to both the transactions and balances tables. Use TRANSACTION logic in my stored procedure layer to ensure that balances and transactions are always in sync. (Supported by Jack.)

    2. Apply transactions to the transactions table and have a trigger that updates the balances table for me with the transaction amount.

    3. Apply transactions to the balances table and have a trigger that adds a new entry in the transactions table for me with the transaction amount.

    I have to rely on security-based approaches to make sure no changes can be made outside of the stored procedures. Otherwise, for example, some process could directly insert a transaction into the transactions table and under scheme 1.3 the relevant balance would be out of sync.

  2. Have a balances indexed view that aggregates the transactions appropriately. Balances are guaranteed by the storage engine to stay in sync with their transactions, so I don't need to rely on security-based approaches to guarantee this. On the other hand, I cannot enforce balances be non-negative anymore since views -- even indexed views -- cannot have CHECK constraints. (Supported by Denny.)

  3. Have just a transactions table but with an additional column to store the balance effective right after that transaction executed. Thus, the latest transaction record for a user and currency also contains their current balance. (Suggested below by Andrew; variant proposed by garik.)

When I first tackled this problem, I read these two discussions and decided on option 2. For reference, you can see a bare-bones implementation of it here.

    Question Protected by Paul White says GoFundMonica
4 added 12 characters in body; edited tags
source | link
  1. Have a separate balances tableHave a separate balances table and do one of the following:

    1. Apply transactions to both the transactions and balances tables. Use TRANSACTION logic in my stored procedure layer to ensure that balances and transactions are always in sync. (Supported by Jack.)

    2. Apply transactions to the transactions table and have a trigger that updates the balances table for me with the transaction amount.

    3. Apply transactions to the balances table and have a trigger that adds a new entry in the transactions table for me with the transaction amount.

    I have to rely on security-based approaches to make sure no changes can be made outside of the stored procedures. Otherwise, for example, some process could directly insert a transaction into the transactions table and under scheme 1.3 the relevant balance would be out of sync.

  2. Have a balances indexed viewHave a balances indexed view that aggregates the transactions appropriately. Balances are guaranteed by the storage engine to stay in sync with their transactions, so I don't need to rely on security-based approaches to guarantee this. On the other hand, I cannot enforce balances be non-negative anymore since views -- even indexed views -- cannot have CHECK constraints. (Supported by Denny.)

  3. Have just a transactions tableHave just a transactions table but with an additional column to store the balance effective right after that transaction executed. Thus, the latest transaction record for a user and currency also contains their current balance. (Suggested below by Andrew; variant proposed by garik.)

  1. Have a separate balances table and do one of the following:

    1. Apply transactions to both the transactions and balances tables. Use TRANSACTION logic in my stored procedure layer to ensure that balances and transactions are always in sync. (Supported by Jack.)

    2. Apply transactions to the transactions table and have a trigger that updates the balances table for me with the transaction amount.

    3. Apply transactions to the balances table and have a trigger that adds a new entry in the transactions table for me with the transaction amount.

    I have to rely on security-based approaches to make sure no changes can be made outside of the stored procedures. Otherwise, for example, some process could directly insert a transaction into the transactions table and under scheme 1.3 the relevant balance would be out of sync.

  2. Have a balances indexed view that aggregates the transactions appropriately. Balances are guaranteed by the storage engine to stay in sync with their transactions, so I don't need to rely on security-based approaches to guarantee this. On the other hand, I cannot enforce balances be non-negative anymore since views -- even indexed views -- cannot have CHECK constraints. (Supported by Denny.)

  3. Have just a transactions table but with an additional column to store the balance effective right after that transaction executed. Thus, the latest transaction record for a user and currency also contains their current balance. (Suggested below by Andrew; variant proposed by garik.)

  1. Have a separate balances table and do one of the following:

    1. Apply transactions to both the transactions and balances tables. Use TRANSACTION logic in my stored procedure layer to ensure that balances and transactions are always in sync. (Supported by Jack.)

    2. Apply transactions to the transactions table and have a trigger that updates the balances table for me with the transaction amount.

    3. Apply transactions to the balances table and have a trigger that adds a new entry in the transactions table for me with the transaction amount.

    I have to rely on security-based approaches to make sure no changes can be made outside of the stored procedures. Otherwise, for example, some process could directly insert a transaction into the transactions table and under scheme 1.3 the relevant balance would be out of sync.

  2. Have a balances indexed view that aggregates the transactions appropriately. Balances are guaranteed by the storage engine to stay in sync with their transactions, so I don't need to rely on security-based approaches to guarantee this. On the other hand, I cannot enforce balances be non-negative anymore since views -- even indexed views -- cannot have CHECK constraints. (Supported by Denny.)

  3. Have just a transactions table but with an additional column to store the balance effective right after that transaction executed. Thus, the latest transaction record for a user and currency also contains their current balance. (Suggested below by Andrew; variant proposed by garik.)

    Bounty Ended with Andrew Bickerton's answer chosen by Nick Chammas
3 added proposed solution to list of options; linked various options to answers supporting them
source | link

I expect this database to accept hundreds of thousands of new transactions per day, as well as balance queries on a higher order of magnitude. To serve up balances very quickly I need to pre-aggregate them. At the same time, I need to guarantee that a balance never contradicts its transaction history. To serve up balances very quickly I need to pre-aggregate them. At the same time, I need to guarantee that a balance never contradicts its transaction history.

  1. Have a separate balances table and do one of the following:

    1. Apply transactions to both the transactions and balances tables. Use TRANSACTION logic in my stored procedure layer to ensure that balances and transactions are always in sync. (Supported by Jack.)

    2. Apply transactions to the transactions table and have a trigger that updates the balances table for me with the transaction amount.

    3. Apply transactions to the balances table and have a trigger that adds a new entry in the transactions table for me with the transaction amount.

    I have to rely on security-based approaches to make sure no changes can be made outside of the stored procedures. Otherwise, for example, some process could directly insert a transaction into the transactions table and under scheme 1.3 the relevant balance would be out of sync.

  2. Have a balances indexed view that aggregates the transactions appropriately. Balances are guaranteed by the storage engine to stay in sync with their transactions, so I don't need to rely on security-based approaches to guarantee this. On the other hand, I cannot enforce balances be non-negative anymore since views -- even indexed views -- cannot have CHECK constraints. (Supported by Denny.)

  3. Have just a transactions table but with an additional column to store the balance effective right after that transaction executed. Thus, the latest transaction record for a user and currency also contains their current balance. (Suggested below by Andrew; variant proposed by garik.)

After readingWhen I first tackled this problem, I read these two discussions, I and decided on option 2. For reference, you can see a bare-bones implementation of it here.

ToArchiving transactions:

Let me elaborate a bit on archiving transactions and the "summary transactions" I mentioned above:. First, regular archiving will be a necessity in a high-load system like this. I want to maintain consistency between balances and their transaction histories while allowing old transactions to be moved somewhere else. To do this I'll replace every batch of archived transactions with a summary of their amounts per user and currency.

I expect this database to accept hundreds of thousands of new transactions per day, as well as balance queries on a higher order of magnitude. To serve up balances very quickly I need to pre-aggregate them. At the same time, I need to guarantee that a balance never contradicts its transaction history.

  1. Have a separate balances table and do one of the following:

    1. Apply transactions to both the transactions and balances tables. Use TRANSACTION logic in my stored procedure layer to ensure that balances and transactions are always in sync.

    2. Apply transactions to the transactions table and have a trigger that updates the balances table for me with the transaction amount.

    3. Apply transactions to the balances table and have a trigger that adds a new entry in the transactions table for me with the transaction amount.

    I have to rely on security-based approaches to make sure no changes can be made outside of the stored procedures. Otherwise, for example, some process could directly insert a transaction into the transactions table and under scheme 1.3 the relevant balance would be out of sync.

  2. Have a balances indexed view that aggregates the transactions appropriately. Balances are guaranteed by the storage engine to stay in sync with their transactions, so I don't need to rely on security-based approaches to guarantee this. On the other hand, I cannot enforce balances be non-negative anymore since views -- even indexed views -- cannot have CHECK constraints.

After reading these two discussions, I decided on option 2. For reference, you can see a bare-bones implementation of it here.

To elaborate a bit on archiving transactions and the "summary transactions" I mentioned above: First, regular archiving will be a necessity in a high-load system like this. I want to maintain consistency between balances and their transaction histories while allowing old transactions to be moved somewhere else. To do this I'll replace every batch of archived transactions with a summary of their amounts per user and currency.

I expect this database to accept hundreds of thousands of new transactions per day, as well as balance queries on a higher order of magnitude. To serve up balances very quickly I need to pre-aggregate them. At the same time, I need to guarantee that a balance never contradicts its transaction history.

  1. Have a separate balances table and do one of the following:

    1. Apply transactions to both the transactions and balances tables. Use TRANSACTION logic in my stored procedure layer to ensure that balances and transactions are always in sync. (Supported by Jack.)

    2. Apply transactions to the transactions table and have a trigger that updates the balances table for me with the transaction amount.

    3. Apply transactions to the balances table and have a trigger that adds a new entry in the transactions table for me with the transaction amount.

    I have to rely on security-based approaches to make sure no changes can be made outside of the stored procedures. Otherwise, for example, some process could directly insert a transaction into the transactions table and under scheme 1.3 the relevant balance would be out of sync.

  2. Have a balances indexed view that aggregates the transactions appropriately. Balances are guaranteed by the storage engine to stay in sync with their transactions, so I don't need to rely on security-based approaches to guarantee this. On the other hand, I cannot enforce balances be non-negative anymore since views -- even indexed views -- cannot have CHECK constraints. (Supported by Denny.)

  3. Have just a transactions table but with an additional column to store the balance effective right after that transaction executed. Thus, the latest transaction record for a user and currency also contains their current balance. (Suggested below by Andrew; variant proposed by garik.)

When I first tackled this problem, I read these two discussions and decided on option 2. For reference, you can see a bare-bones implementation of it here.

Archiving transactions:

Let me elaborate a bit on archiving transactions and the "summary transactions" I mentioned above. First, regular archiving will be a necessity in a high-load system like this. I want to maintain consistency between balances and their transaction histories while allowing old transactions to be moved somewhere else. To do this I'll replace every batch of archived transactions with a summary of their amounts per user and currency.

2 expanded on archiving solution
source | link
    Bounty Started worth 50 reputation by Nick Chammas
    Tweeted twitter.com/#!/StackDBAs/status/112553201269620736
1
source | link