Billing System ERD (Simple Version)

Welcome to a quick sharing of an ERD of a billing system. But before we start, “billing” can cover a wide range of services and products. So just a quick disclaimer – I tried to keep this as generic as possible. Feel free to change the structure to fit your own needs, read on!

 

 

TABLE OF CONTENTS

 

 

SIMPLE BILLING SYSTEM

To keep this system simple, I have generalized the entities into 3 groups.

• Users – To address the elephant, the users of the system; The customers, and staff members.
• Bills – You can also call this “invoice” to sound more “professional”.
• Payment – Self-explanatory, the payment history for each bill/invoice.

 

 

FOR REFERENCE ONLY

Once again, before the not-so-intelligent “expert trolls” start to scream “the diagram is not accurate” – This is only an example for your reference. Some assumptions were made.

• A bill has at least one item. If you prefer “one item and one lump sum only”, go ahead and make the changes – Remove the bill items entirely.
• If the company only accepts cash (in full), feel free to remove the payments table.
• If customers are registered only upon making a purchase, feel free to change the customer-bills relationship to one-to-one/many.

This list can go on forever. You are building your own system now, you decide your own rules.

 

 

PART 1) USERS

1A) CUSTOMERS TABLE

COLUMN	DATA TYPE	NOTES
customer_id	INTEGER	Primary key.
customer_name	VARCHAR	The customer’s name.
customer_tel	VARCHAR	Customer’s telephone number.
customer_email	VARCHAR	Unique, customer’s email.
customer_address	VARCHAR	Customer’s address.

This table is pretty self-explanatory. But depending on the situation, things can get sticky. For example, a customer can have multiple addresses – Home address, office address, outlet address, etc… In this case, it is better to create another “address book” table. Let the customer set their own default home/billing/delivery address.

 

 

1B) STAFF TABLE

COLUMN	DATA TYPE	NOTES
staff_id	INTEGER	Primary key.
staff_name	VARCHAR	Staff name.
staff_email	VARCHAR	Staff email.
staff_password	VARCHAR	Staff password.

I don’t usually like to keep multiple “user tables”. But customers and staff are separate for a good reason – Customers can be from a different system. For example, an ordering portal. In this case, it is not a part of this billing system entirely; It is only right to separate the customers and staff in this case.

 

PART 2) BILLS

2A) “MAIN” BILLS TABLE

COLUMN	DATA TYPE	NOTES
bill_id	INTEGER	Primary Key.
customer_id	INTEGER	Foreign key, bill to this customer.
staff_id	INTEGER	Foreign key, the staff who created this bill.
bill_date	DATE	Date/time when the bill is created.
bill_due	DATE	Payment due date.
bill_notes	VARCHAR	Additional notes, if any.
bill_name	VARCHAR	Bill to.
bill_tel	VARCHAR	Contact number.
bill_email	VARCHAR	Email address.
bill_address	VARCHAR	Street address.

Some of you sharp ones should have realized – Customer ID is a foreign key here. Why keep the “extra” billing name/email/tel/address? Let us remove the “extra fields” from the table.

• Staff A created the bill today for customer B.
• Customer B moved one month later, and staff A updated the customer’s address.
• All the previous bills for customer B are now inaccurate; Old bills should still be tied to the old address.

There you go. This is not “extra”, but “integrity” – Accurate at the time of entry.

 

 

2B) BILL ITEMS TABLE

COLUMN	DATA TYPE	NOTES
item_id	INTEGER	Primary Key.
bill_id	INTEGER	Foreign Key.
item_name	VARCHAR	Product sold or service provided.
item_amount	DECIMAL	Price for this item.

For you guys who are sure that “every bill only has one item” – Keep this table for future-proofing. It doesn’t hurt.

 

2C) BILL TOTALS TABLE

COLUMN	DATA TYPE	NOTES
total_id	INTEGER	Primary Key.
bill_id	INTEGER	Foreign Key.
total_name	VARCHAR	Sub-total, discount, tax, grand total, etc…
total_amount	DECIMAL	Total amount.

That’s right – Tax rates, discounts, and service charges can change over time. Use this table to keep that information.

 

PART 3) PAYMENT

COLUMN	DATA TYPE	NOTES
payment_id	INTEGER	Primary key.
bill_id	INTEGER	Foreign key.
payment_mode	VARCHAR	Up to you to decide – Cash, credit card, online banking, voucher, etc…
payment_amount	VARCHAR	Amount paid.
payment_status	VARCHAR	Up to you to decide – Accepted, rejected, canceled, bounced, etc…
payment_notes	VARCHAR	Notes, if any.

Finally, a table to hold payment details and history. Well, customers can pay half by cash, and half by credit card. This table will keep the options open.

 

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart