sql table info needed

[Rohithzr]

I have a table that manages the balance,advance,current payments of all customers, so it tends to have approx +10,000 rows each month

so my question is should i divide the table in to two say current payments and advance+balance table or should i leave like it is.

NOTES:
nearly 70-80% queries will end up merging the two tables
doing this will require loads of changes in the codes

[__]

I’m not exactly sure what your question is, but no, payments and balances are not the same thing — they should be in separate tables. In fact, balances should not be recorded at all: payments should be positive or negative (money in or out), and the balance should be calculated as the sum of payments. I’m not sure what you mean by “advance.”

In any case, if you need further help, please show your DB schema and describe your question in more detail.

[Rohithzr]

Actually it is a bit typical and that’s why there is a balance entry.
its something like this:
1 there can be multiple plans per customer
2 he is allowed at times to do a partial payment
3 in case of partial payment i need to store date of both payments
4 customer may pay any amount in advance
5 i have to then check for advance payments and do automatic entries in bulk
6 money is only coming in not going out, hence cant really be negative
7 i have been working on “blank/no entry” means “balance” concept up till now but i need to store every months payable amount in database and calculate amount for each month accordingly
8 i have to mostly stick to the current database until i make a new software which will need time

it can be confusing, even i get confused so tell me something if you understand something

[__]

i have to mostly stick to the current database until i make a new software which will need time

From your original post, I thought you were asking for advice on changing your database. If that’s not the case, then what is your immediate goal?

money is only coming in not going out, hence cant really be negative

Well, any charge against the account (e.g., the monthly bill) is conceptually a “negative” amount.

If you were redesigning the DB, I would suggest something like:

create table transactions(
  id serial primary key,
  account bigint unsigned not null,
  amount decimal(10,2) not null,
  completed datetime not null,
  description text,
  foreign key(account) references account(id)
);

A charge might look something like:

| id | account | amount | completed           | description     |
|----+---------+--------+---------------------|-----------------|
| 1  | 1234    | -99.95 | 2014-07-01 00:00:00 | monthly service |
|----+---------+--------+---------------------|-----------------|

while a payment might look like:

| id | account | amount | completed           | description     |
|----+---------+--------+---------------------|-----------------|
| 2  | 1234    | 100.00 | 2014-07-08 14:32:09 | payment         |
|----+---------+--------+---------------------|-----------------|

If you wanted the current balance for this account, you could do:

select sum(amount) balance from transactions where account=1234

…which, in the case of this example, would return:

| balance |
|---------|
| 0.05    |
|---------|

This model would meet all of your requirements, including partial or advance payments.

Each account references a row in an account table, which describes the account and the customer it belongs to, so multiple accounts per customer are not a problem either.

To find advance payments, you just have to check whether the account balance is positive (as in the example above).

If you want to keep the current database, show me your current schema (you can use show create table{your table name goes here};), and we can talk about the specific problem you want to solve.

[Rohithzr]

First I’ll probably change the db otherwise it will be an headache
Second I currently on mobile so will upload schema as soon as I get home

Now your idea is good, but won’t it require me to charge all the customers with an amount at beginning of the month
Cause that would mean 10,000 entries every month and then another 10,000 if they all pay?

PayId amount. Pack Status. DueDate. Clearingdate. TransTime cId
[IMG]http://i.imgur.com/SOMJmKv.png[/IMG]

What I am doing currently : let the user do payments whole month by taking the current package from customers table, secondly.
With every payment i store the package amount in a column (to know what package was active when)
So if a customer with 500 as package who pays 200 it looks something like this

123 | 200 | 500 | 0 | 2014-01-01 | NULL | 2014-01-01 00:00:00 | 177
124 | 300 | 0 | 5 | 2014-01-01 | NULL | 2014-01-01 00:00:00 | 177

when he clears the pending payment id 124 i update
124 | 300 | 0 | **9** | 2014-01-01 | 2014-02-01 00:00:00 | 2014-01-01 00:00:00 | 177

I calculate payments with date columns
And If he doesn’t do any payments

Only then at the end of the month I do an automatic entry

127 | 0 | 500 | 5 | 2014-01-01 | NULL | 2014-01-01 00:00:00 | 177

Hence reducing the the number of queries to 10,000 + partial balance entries

So ur thoughts …. And ur idea is goood
Working on it

Edit I don’t know what the hell happend to formatting I’ll correct it in a few mint …. Nearly home
Edit: I am home :)

[Rohithzr]

list of my status variable: should help

0: paid amount
3: deleted amount [i dont really want to delete the payment do i]
5: pending amount [the amount he had to pay but he did not]
6: advance deposit [will use this to cover next months payment]
9: balance cleared [once the balance is cleared i store a clearing date]
10: advance cleared [once an advance amount is used to clear it gets cleared with a clearing Date]

[Rohithzr]

My table schema

CREATE TABLE payments (
 payId int(8) NOT NULL AUTO_INCREMENT,
 amount decimal(6,2) NOT NULL,
 pack decimal(6,2) NOT NULL DEFAULT '0.00',
 date date NOT NULL,
 clearingDate timestamp NULL DEFAULT NULL,
 time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 status tinyint(1) NOT NULL DEFAULT '0',
 opId int(4) NOT NULL,
 cId int(7) NOT NULL,
 lnId int(4) NOT NULL DEFAULT '0',
 PRIMARY KEY (payId),
 KEY cId (cId),
 KEY opId (opId),
 CONSTRAINT payments_ibfk_1 FOREIGN KEY (cId) REFERENCES customers (cId),
 CONSTRAINT payments_ibfk_2 FOREIGN KEY (opId) REFERENCES operators (opId)
) ENGINE=InnoDB AUTO_INCREMENT=149658 DEFAULT CHARSET=latin1

[__]

Now your idea is good, but won’t it require me to charge all the customers with an amount at beginning of the month Cause that would mean 10,000 entries every month and then another 10,000 if they all pay?

Yes. This shouldn’t be an issue if everything is properly indexed.

The “charge” records could all be inserted automatically (e.g., a cron job) based on what package each customer is subscribed to. If you do it during off hours (early morning, for example), then it shouldn’t impact any other usage.

Question:

Is this all managed internally to your company (i.e., you/ an employee updates the DB) or is it connected to a customer-facing app (or similar, e.g., customers can view their account details and pay on your website)?

status tinyint(1) NOT NULL DEFAULT '0',

This should be a foreign key to a table that defines each of your variables.
(Also, '0' is a string, not a tinyint: should default to 0.)

[Rohithzr]

Yes. This shouldn’t be an issue if everything is properly indexed.

If what you are saying is right “means that it wont increase server load” then it will be the most appropriate approach and i wouldn’t know how to thank you for that. because even though i will have to do a lot of coding to make it work this way but still it should work smoothly in the future

Is this all managed internally …. customer-facing app

well first it is a web application that is used by operators[our direct customers] and then each operator has thousands of subscribers [the final customers] but 90% of the entries [and load] comes from a HandHeld, API based payment machine connected through GPRS these entries are done from the “operators end”

This should be a foreign key to a table that defines each of your variables.

Well, actually it began with only two possible keys and went onto become 6-7 but still as they are quite fixed value i have declared it as an array on top of the pages required and print it something like this$status[$row['status']]

status tinyint(1) NOT NULL DEFAULT ‘0’

well thanks i never noticed it because i made the db through GUI of PHPmyAdmin never noticed that it makes it a string instead on tinyint. Thanks for that

your suggestion seems to be problem solver for me.
Thanks a lots and lots.

[__]

If what you are saying is right “means that it wont increase server load” …

Well, logically, the server will have more work to do — but it should be entirely manageable if things are set up well. The way you build your queries and indexes will be important.

This sort of application is also a prime candidate for using stored procedures, if you’re interested in learning about them. It could benefit efficiency, accuracy, and security.

Most importantly, test, test, test …!
Switching between backends will be the most difficult part, so make sure to develop the new version independently, and make sure you can switch back quickly if need be.

Any other questions, let me know.

i made the db through GUI of PHPmyAdmin never noticed that …

Well, at the end of the day, it’s no big deal — MySQL will simply convert that '0' to 0 on insert. Under default settings, it won’t even issue a warning. It’s still good to be aware of such things, though.

it began with only two possible keys and went onto become 6-7 …

Which is how things typically develop. : )

[Author]

Posts