"Keychain" <someone@somewhere.com> wrote in message
news:ZpmdnUZj29vA8k7anZ2dnUVZ_jCdnZ2d@comcast.com. ..
> "Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
> news:E8OdnRTiUNNBMFjanZ2dnUVZ_r2nnZ2d@comcast.com. ..
>> Keychain wrote:
>>> "Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
>>> news:T9KdnXZO-7lxR1nanZ2dnUVZ_hKdnZ2d@comcast.com...
>>>> Keychain wrote:
>>>>> I need to create a table that records monetary-like (non-ecommerce)
>>>>> transactions.
>>>>>
>>>>> The structure looks like this:
>>>>>
>>>>> tid, uid, amount, balance, type, dateline
>>>>>
>>>>> The data looks like this:
>>>>>
>>>>> 1, 1, -10, -10, debit, 1202651856
>>>>> 2, 1, 3, -7, credit, 1202652000
>>>>>
>>>>> A running balance is kept, which makes it easy to pull the newest
>>>>> record for the current balance.
>>>>>
>>>>
>>>> Don't keep a running balance. Just total the amounts for an account.
>>>> If the table gets too big, do it like the banks do - have a starting
>>>> balance for the month and total from there.
>>>>
>>>>> The debit/credit amounts are stored as positive and negative values.
>>>>>
>>>>
>>>> That's how I would do it.
>>>>
>>>>> Is there anything flawed about this approach?
>>>>>
>>>>
>>>> Just ensure you are encrypting any sensitive information (i.e. if this
>>>> were an ecommerce database, encrypt the credit card info).
>>>>
>>>>> Should the balance be kept in a separate table? Exp. table structure:
>>>>> uid, balance
>>>>>
>>>>
>>>> Nope. See above.
>>>>
>>>>> Should the amounts all be stored as absolute positive values?
>>>>>
>>>>
>>>> Definitely NOT.
>>>>
>>>>> What about having separate debit and credit columns and skipping the
>>>>> type indicator? This would mean one of the columns for each record
>>>>> would always be zero.
>>>>>
>>>>
>>>> Nope. And why do you need a type indicator? You can tell which one is
>>>> is based on the amount being positive or negative.
>>>>
>>>>> Any other thoughts/suggestions?
>>>>>
>>>>> Thanks in advance.
>>>>>
>>>>
>>>> KISS. Don't add more data than you need, and definitely don't
>>>> duplicate data. Try reading up on "database normalization". Google
>>>> will give you several good hits for tutorials.
>>>>
>>>> --
>>>> ==================
>>>> Remove the "x" from my email address
>>>> Jerry Stuckle
>>>> JDS Computer Training Corp.
>>>> jstucklex@attglobal.net
>>>> ==================
>>>>
>>>
>>> If it wasn't clear in my original message, this is NOT a real ecommerce
>>> site so security (from a sensitive financial data perspective) isn't an
>>> issue.
>>>
>>> I will continue to store the amounts in one column as positive/negative
>>> numbers.
>>>
>>> So the balance column isn't necessary. I can simply Sum() the amount
>>> column, but I am concerned about performance down the road. How would
>>> "starting balance for the month" work programmatically and data
>>> structure wise?
>>>
>>> It's true, the amount will indicate debit/credit. However I do need a
>>> type column to distinguish the type of credit/debit for reporting
>>> purposes.
>>>
>>> Thanks to everyone who responded.
>>>
>>
>> Do it like the banks do. On a regular basis, archive the existing
>> transactions and start with a new balance.
>>
>> Or, for instance, you might archive all transactions over six months old
>> and put in a starting balance as of the date.
>>
>> For instance, if you want to archive everything before 1 September, 2007:
>>
>> 1. Start a transaction (using INNODB database)
>> 2. Sum all the transactions up through 31 August and remember.
>> 3. Copy all transactions through 31 August to your archive table
>> 4. Delete all transactions through 31 August
>> 5. Insert a transaction dated 1 September with the balance from Step 2
>> 6. Commit the transaction
>>
>> How many rows are you planning to have in the table, anyway?
>>
>> And yes, I know you said it wasn't a real e-commerce site - that was more
>> for those who are doing e-commerce sites and reading this thread.
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstucklex@attglobal.net
>> ==================
>>
>
> I stumbled across another challenge today. I need to create a ledger type
> report, which will include debit, credit and balance columns. The report
> will use pagination with one or more pages. The trick is how to display
> the running balance. As per the advice given here, I eliminated the
> running balance from the transaction table. I know a running total can be
> calculated per row in the query, but what with a range of rows using
> LIMIT?

After giving this matter some additional thought, it seems like the best way
to go about having a running balance with pagination is to use a separate
query to retrieve the beginning balance at the start of each page. For
example (assuming each page showed 20 records) page 1 retrieves records
1-20, page 2 retrieves records 1-40 and so on. The running balance would be
calculated as the page is being built. This method adds one additional query
per page, which isn't bad.