December 12, 2005 at 9:04 am
I need to design a simple banking application.
The system will be used by employees to purchase company store items...
I figure we'll need at least 2 tables.
Table 1 will store the transaction detail
Table 2 will store the transaction type (ie: Company Deposit, Store Purchase, Etc).
What I am not sure about is the need for a summary table to hold current balance and previous balance. I would like to be able to provide a running balance to our customers, but not real sure if we should store the balance history in the summary table or add a column to the transaction detail table and use a trigger to adjust the balance amount.
December 12, 2005 at 9:30 am
G'day,
The answer is "it depends". Your problem description sounds like a classroom assignment, where the total volume of data, and users, is likely to be quite low. A simple computed column will give you the current balance quite nicely. Prior balance is then nothing more than the current balance from the prior row for that customer.
In general, I try to avoid storing summary data until volume and query traffic are of sufficient volume to rquire a summary table. In general, anything that is stored in two places can and will get out of synch sooner or later.
Please note that my design would likely be different inthe case of a real banking app.
Hope this helps
Wayne
December 12, 2005 at 9:38 am
I appreciate the feed back. You are correct this will be a low volume application. I anticipate a maximum number of simultaneous transactions at 450 - 500.
December 12, 2005 at 10:59 am
Will each customer have a running balance? Why not create a customer table to hold customer info?
December 12, 2005 at 12:02 pm
The reason i didnt create a table to hold customer information is because the customers are our employees and we already have employee tables to hold their info. I will use their employee id to join the bank table to their info. No if someone wouldnt mind helping me create the calculated field....
BTW - i really appreciate everyone's help.
December 12, 2005 at 1:04 pm
How do i make the "Balance" field a calculated field?
example data
Table - Bank_Detail
TransactionID CustomerID Amount Balance Date
1 1 0 0 1/1/2005
2 1 10 10 1/1/2005
3 1 10 20 1/1/2055
4 1 -10 10 1/1/2005
5 2 0 0 1/1/2005
6 2 10 10 1/1/2005
7 2 5 15 1/1/2005
8 2 -10 5 1/1/2005
December 13, 2005 at 8:10 am
Hi,
My definition of a calculated field is NOT a column in a table.
Eliminate the "Balance" column.
If you need to use sql for reports, I'd create a view using a select that would sum the "Amount" column by customerID.
ie Select CustomerID, sum(Amount)
From Bank_Detail
Group by CustomerID
Order by CustomerID
Then, modify the above by joining to your employee table to get name, address info or whatever else you needed for reporting purposes.
Greg H
December 13, 2005 at 8:46 am
I thought of doing that. What would be the performance hit in future? Lets say the system grows to 4000 users with 5 years of transaction activity. Would the performance degrade over time?
December 13, 2005 at 9:21 am
Having a problem posting replies. Hope this one goes through.
Depends on what you mean by "users". Do you mean customers or users that are hitting your db?
If customers, you shouldn't have a problem as long as you properly index your tables. I manage over 1MM customers and need to calculate amounts for invoices, commission payments to our salesforce, etc. Also e-commerce site where we need to calculate order totals, sales tax, freight charges, etc.
Only problem I have is our production printers can't keep up with output.
If you have 4,000 users hitting your db, not sure what to tell you without knowing your server specs.
Hope that helps.
Greg H
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply