Forums

The forums ran from 2008-2020 and are now closed and viewable here as an archive.

Home Forums Back End sql table info needed

  • This topic is empty.
Viewing 10 posts - 1 through 10 (of 10 total)
  • Author
    Posts
  • #175335
    Rohithzr
    Participant

    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

    #175346
    __
    Participant

    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.

    #175347
    Rohithzr
    Participant

    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

    #175350
    __
    Participant

    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.

    #175353
    Rohithzr
    Participant

    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 :)

    #175355
    Rohithzr
    Participant

    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]

    #175356
    Rohithzr
    Participant

    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
    
    #175389
    __
    Participant

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

    #175400
    Rohithzr
    Participant

    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.

    #175423
    __
    Participant

    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. : )

Viewing 10 posts - 1 through 10 (of 10 total)
  • The forum ‘Back End’ is closed to new topics and replies.