asked on

Using MySql to track amounts

I am using PHP and MySql to build a simple event registration. Each user will be given an account #. When they register for the event, they will enter a record that includes: Registration #, account #, f_name, l_name, whether or not they want to register for the convention, how much they owe for that, if they want to register for the banquet, how much they owe for that, and a grand total. From that it will also enter a record into the overall account record: transaction #, Account #, Transaction type (registration/payment), Registration # (if applicable), and transaction amount.

What I am trying to find out is, what kind of data types do I need? I am assuming smallint, maybe mediumint. The payments will be tracked as a negative number (-25) as in paid $25. I wasn't sure how the (-) played into the number of characters and I can't find out how to set the field as signed (if I even need to do that.

This is what I have:
convention amount (max = $5) smallint - 1
banquet amount (max = $20) smallint - 2
total due (max = $25) smallint - 2
transaction total due (could be upwards $4,000 for a vendor to pay his booth registration) mediumint - 6
When I total these sum() will I need to allow for grand total, or do I need to just worry about the individual record.

Roger Baklund

>> what kind of data types do I need?

You can store the amounts in smallint columns, the max values is positive 32.767 and negative -32.768. They are signed by default, so you can assign negative values. You do not need to specify a field width.

I suggest you store positive numbers only, and store payments in a separate table.

You have a registration number and an account number... are both unique? Or could multiple registrations have the same account number?

Maybe you should have a registration date column?

>> When I total these sum() will I need to allow for grand total

You can sum multiple smallint columns, even if the total value exceeds the limit for a smallint.

You should not store the totals, they should be calculated when you need it. Example:

create table Registrations (
  reg_bnr int not null primary key auto_increment,
  account_nbr int not null,
  f_name varchar(30) not null,
  l_name varchar(30) not null,
  convention_amount smallint not null default 0,
  banquet_amount smallint not null default 0,
  unique key(account_nbr),  # unique?
  key(l_name,f_name)
);
 
# All registrations with calculated total amount
select reg_bnr as 'Reg#', 
  account_nbr as 'Account#',
  concat(l_name,', ',f_name) as Name,
  convention_amount as Conv,
  banquet_amount as Banq,
  convention_amount+banquet_amount as Total
from Registrations
order by l_name,f_name
 
# Banquet participants:
select reg_bnr as 'Reg#', 
  account_nbr as 'Account#',
  concat(l_name,', ',f_name) as Name
from Registrations
where banquet_amount > 0
order by l_name,f_name
 
# Sum of all amounts
select 
  sum(convention_amount) as Conv,
  sum(banquet_amount) as Banq,
  sum(convention_amount+banquet_amount) as Total
from Registrations

Open in new window

Defibber

ASKER

I was going to put on each person's "home" their balance owed, so I was going to SELECT sum(transAMT) where account # is ?????, or something like that. Would that still be feasible if payments were in a separate table?

ASKER CERTIFIED SOLUTION

Roger Baklund

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roger Baklund

I don't think you need Account#, you could store the Reg# in the payment table and use that to link a payment to a registration. A separate Account# would be usefull if multiple registrations was to be paid by a single payment, for instance if a company would pay for multiple participants.

Defibber

ASKER

A little over my head, but I should be able to figure it out from here.

Defibber

ASKER

I was planning on the user to be able to register themselves and several other people as well, so after they were finished registering the people, they would go to a "print invoice" page that would list out all of the registrations they made with a total and they would submit the invoice, check/money order to use for processing.

I am assuming the "R.account_nbr as 'Account#'" is Registrations.account_nbr. What does group by 1,2,3,4 do? I understand group by, but not sure where the 1,2,3,4 comes from.

Roger Baklund

"group by 1,2,3,4" simply means to group by the four first columns. In this case it is the same as "group by reg_bnr,R.account_nbr,concat(l_name,', ',f_name),convention_amount+banquet_amount"

With a single invoice for multiple registrations, you also need to know what name to put on the invoice, probably a company name in some cases. This should be stored in a third separate table.

You should read about normalization:

http://en.wikipedia.org/wiki/Database_normalization
http://databases.about.com/od/specificproducts/a/normalization.htm

Defibber

ASKER

Thanks!, The name would be the user making the reservation which is in a separate table. I kind of figured that is what you meant by the 1,2,3,4.