Home › Forums › Back End › sql table info needed › Reply To: sql table info needed
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.