DB Design Payments Table

Question

I'm working on a Desktop application that uses a DB to store information i.e (customers, houses, proprietors, payments). So the application must accept payments from customers to company, company to customers(in case of a refund), company to proprietor, proprietor to company. At this moment i'm thinking about this Payments table(there is more columns like dates, quantity).

| id            | int(11)     | NO   | PRI | NULL    | auto_increment |

| type          | varchar(20) | YES  |     | NULL    |                |

| reference_id  | int(11)     | YES  |     | NULL    |                |

With this table i was going to use id as the unique payment id, type to tell if it's a payment from company, proprietor, customer,etx.. and the reference_id is the unique id of the proprietor, customer, etx (the reason i store a reference_id is to find all the payments sent or received to that person)

For some reason it feels like an ugly design not only because i'm not using many-to-many relationship but also it gets kinda tricky to fetch data because you have to specify the type.

What would be a better design?

Answer 1

A better design would be to have a single table to hold all the parties to a payment. If necessary, sub-type this table so companies, proprietors and customers have their own, unique sets of columns (or a separate, related table).

The payments table will then have two foreign keys to Parties, let's call them PaymentFromPartyID and PaymentToPartyID, along with amount, date etc. This way all permutations of money transfer can be tracked.

To find all the monies a person has received use

select
    sum(p.Amount)
from Payments as p
inner join Parties as y
    on y.PartyID = p.PaymentToPartyID
where y.Name = 'The person/company/proprietor of interest';

Additional Detail

A party is anyone or anything who has in interest in a payment. This would be a proprietor, customer or company, distinguished by a type:

Parties(
    PartyID
    PartyName
    PartyType     -- customer or company or proprietor
    <customer-fields>
    <proprietor-fields>
    <company-fields>)

A payment relates a payer (of any type) to a payee (of any type):

Payments(
    PaymentFromPartyID  -- FK to Parties.PartyID
    PaymentToPartyID    -- FK to Parties.PartyID
    Amount
    Date
    etc.)

The IDs used in From and To says in which direction the money flows. There is no need to have any information about a party in the Payments table, such as a type, other than the two PartyIDs.

To get all the payments made to customers (for example) add Parties.PartyType = 'Customer' to the query given above.

The Payments_Track table you list has all the attributes I list here (bar a few surrogate IDs) but is normalised.

Answer 2

First create a table for reference id if it does not exist

| ref_id            | int(11)     | NO   | PRI | NULL    | auto_increment |
| reference_id      | int(11)     | YES  |     | NULL    |                |

then create another table to store the types

| type_id            | int(11)     | NO   | PRI | NULL    | auto_increment |
| type               | varchar(20) | YES  |     | NULL    |                |

create another table to match the type with the reference

| id               | int(11)     | NO   | PRI | NULL    | auto_increment |
| type_id          | int(11)     | YES  |     | NULL    |                |
| ref_id           | int(11)     | YES  |     | NULL    |                |

This way, you can easily add more types in the future, get the list if available types too. Makes it neat and kinda organised.