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 Reply To: sql table info needed

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