SEO

"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.

Keychain wrote:
> "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.

It also sounds like you really need to do what should have been done in
the first place. Sit down with the "customer" and determine all of the
absolute requirements for the project. This is a lost art in today's
business where RAD (rapid application development outweighs multiple
ReRAD (re doing the same application over and over because someone can't
make up their mind or truly do not know what they want).

What you really need is a report-writer not just the SQL.
Report-writers use multiple sql-statements to "build" the report to suit
the requirements (Think billing/invoice statements from your vendors or
that "pretty-looking" report for the bean-counters/execs).

Oracle incorporated a lot of the page formating syntax to build those
sorts of reports and pagination etc... and I am sure some companies
still use those reports, but I do not see that sort of formating within
the MySQL engine(s) - I have not read the docs cover-to-cover yet...
but, I also could not find those sort of things using google or the
search feature on the MySQL site either...

"Michael Austin" <maustin@firstdbasource.com> wrote in message
news:MnXAj.25487$J41.21661@newssvr14.news.prodigy. net...
> Keychain wrote:
>> "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.
>
> It also sounds like you really need to do what should have been done in
> the first place. Sit down with the "customer" and determine all of the
> absolute requirements for the project. This is a lost art in today's
> business where RAD (rapid application development outweighs multiple ReRAD
> (re doing the same application over and over because someone can't make up
> their mind or truly do not know what they want).
>
> What you really need is a report-writer not just the SQL. Report-writers
> use multiple sql-statements to "build" the report to suit the requirements
> (Think billing/invoice statements from your vendors or that
> "pretty-looking" report for the bean-counters/execs).
>
> Oracle incorporated a lot of the page formating syntax to build those
> sorts of reports and pagination etc... and I am sure some companies still
> use those reports, but I do not see that sort of formating within the
> MySQL engine(s) - I have not read the docs cover-to-cover yet... but, I
> also could not find those sort of things using google or the search
> feature on the MySQL site either...

Thanks for the response, but I don't need a lecture about how to scope a
project. Your answer has absolutely nothing to do with my question (or the
thread.) Perhaps you should take your own advice and take the time to
educate yourself before jumping to conclusions.

Keychain wrote:
> "Michael Austin" <maustin@firstdbasource.com> wrote in message
> news:MnXAj.25487$J41.21661@newssvr14.news.prodigy. net...
>> Keychain wrote:
>>> "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.
>>
>> It also sounds like you really need to do what should have been done
>> in the first place. Sit down with the "customer" and determine all of
>> the absolute requirements for the project. This is a lost art in
>> today's business where RAD (rapid application development outweighs
>> multiple ReRAD (re doing the same application over and over because
>> someone can't make up their mind or truly do not know what they want).
>>
>> What you really need is a report-writer not just the SQL.
>> Report-writers use multiple sql-statements to "build" the report to
>> suit the requirements (Think billing/invoice statements from your
>> vendors or that "pretty-looking" report for the bean-counters/execs).
>>
>> Oracle incorporated a lot of the page formating syntax to build those
>> sorts of reports and pagination etc... and I am sure some companies
>> still use those reports, but I do not see that sort of formating
>> within the MySQL engine(s) - I have not read the docs cover-to-cover
>> yet... but, I also could not find those sort of things using google
>> or the search feature on the MySQL site either...
>
> Thanks for the response, but I don't need a lecture about how to scope a
> project. Your answer has absolutely nothing to do with my question (or
> the thread.) Perhaps you should take your own advice and take the time
> to educate yourself before jumping to conclusions.
>
>

Actually, I think Michael is quite correct. These are the types of
questions which make a difference in a project. In some cases, it
changes the database design; it can even affect the selection of the
RDBMS you should be using.

I also agree with him that now you need a report writer. I'd be looking
at another language to write the report and not try to do it all in SQL.

You probably could do it all in SQL, but it wouldn't be easy.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

"Keychain" <someone@somewhere.com> wrote in message
news:t5WdnbKr-pzjhEnanZ2dnUVZ_uevnZ2d@comcast.com...
> "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.

A quick follow-up.

It turns out the BEST way to go about this is calculate a running balance
using a user-defined variable in a temporary table query. It's then easy to
return a range of records for pagination.