Linked Questions

101
votes
5answers
28k views

Storing vs calculating aggregate values

Are there any guidelines or rules of thumb to determine when to store aggregate values and when to calculate them on the fly? For example, suppose I have widgets which users can rate (see schema ...
31
votes
5answers
3k views

Is it OK to keep a value which updates in a table?

We are developing a platform for prepaid cards, which basically holds data about cards and their balance, payments, etc. Up until now we had a Card entity which has a collection of Account entity, ...
21
votes
1answer
9k views

How to get the MAX row

In SQL Server I've always found it a pain to get the max rows for a dataset, I'm looking for a list of the methods to retrieve the max rows with some guidance on performance and maintainability. ...
11
votes
1answer
5k views

Synchronization using triggers

I have a requirement similar to previous discussions at: Writing a simple bank schema: How should I keep my balances in sync with their transaction history? Trigger in combination with transaction I ...
5
votes
2answers
3k views

Design best practices for last tracking with indexed view

My tables structure is below : TbDoc (ID int , ...) TbDocActions( ID Int, DocID Int, Date DateTime, col1 int, col2 int, ...) I want to have indexed view to get last TbDocActions columns for each ...
7
votes
2answers
2k views

Using totals on aggregates to improve performance

I have two tables: details and totals of these details. Details (Slow solution): select OrderId = r.OrderId , TotalQty = SUM(r.Quantity) , ...
9
votes
1answer
9k views

Double Entry Accounting Schema

Setting up a double entry accounting system for personal use and to help manage a really small business. Trying to put a few features that seem relevant now. Business Rules The logic, for those not ...
8
votes
3answers
296 views

How can I get running totals of recent rows faster?

I'm currently designing a transaction table. I realized that calculating running totals for each row will be needed and this might be slow in performance. So I created a table with 1 million rows for ...
4
votes
2answers
2k views

Add constraint to ensure flag column is <= sum of column in other table

I have a column RSEFlag that contains a flag value that is the sum of one or more of 1, 2, 4. I have separate table that contains the description of each of those flag values: RSECode RSEDescription -...
1
vote
1answer
4k views

Designing a bank database schema with multiple balances per account [closed]

I'm trying to build a schema to keep a record of the transactions made on some accounts, but those accounts are not "user accounts", instead there are orders that are being paid, and money is being ...
2
votes
0answers
1k views

How to calculate running total based on dynamic values?

I need to calculate the running total in which the target column is changing along with the time. Its something which aws does. Charging based for only what you use. So how do I aggregate sum of ...
0
votes
2answers
93 views

How to write SQL queries to populate the tables while synchronizing transactions and account balance?

I have two tables having the following schema. CREATE TABLE users( `id` INT AUTO_INCREMENT, `balance` DECIMAL(10, 2), PRIMARY KEY(`id`) ); CREATE TABLE user_transactions( `id` INT ...