Re: VALUE MANIPULATION IN MySQL with JAVA question..
justineee wrote
I think I need to recompute the balance on each transaction because I
am doing this for the three column running balance for the ledger.
Do you really need the historic balance as of each transaction, or
only the current balance?
Matt Humphrey wrote:
Consider that every time you insert you have to read the entire contents =
of
the account. If the user has 2 transactions per day for 5 years that's=
3000
terms you read and add up every time you insert a new record. What mak=
es
this particularly wasteful is that the number you are computing is alread=
y
present as the balance of the previous transaction. You can write a
getBalance () query that simply finds the transaction with the largest id
and returns the balance of that row.
To normalize further and more correctly, keep the current balance in a
separate table, IDed by the account. Correlate the individual
transactions to the account table via a foreign key.
Matt also wrote:
Every row will have a unique number here. Most databases have a way to
automatically generate this key--just make sure it is sequential and
increasing. Look for a column attribute called "auto increment" and set =
the
first row to 0. After that each insert adds one automatically.
Each insert adds at least one; it's not guaranteed to add only one.
This may be more than you need to know yet, justineee, but that auto-
incremented key should not be visible to the view layer. It is
strictly a data-layer internal housekeeping construct, an artificial
key that stands in as a surrogate (a "surrogate key") for the key
expressed in business-domain terms. It is the business-domain key, or
"natural key", that should be visible to users. The natural key can
and often does involve more than one column of the table. The
business logic can keep track of the correlation between the natural
key and the internal surrogate key.
Surrogate, auto-incremented (sequenced) keys exist to be smaller and
faster than natural keys, and to simplify handling of certain rare
data anomalies. Not everyone approves of them. In your ledger
application, you might need to invent a user-visible "transaction
ID". Such a key might be a numeric string (not a number, because you
never do calculations with it), or some formatted combination of date,
time and sequence, or something arbitrary but sensible to users.
For your needs, follow Matt's advice, including careful study of third
normal form. Do avoid revealing internal keys to users.
--
Lew