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
First, here is the entire ER diagram and a quick overview of the system.
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.
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) USERS
1A) CUSTOMERS TABLE
|customer_name||VARCHAR||The customer’s name.|
|customer_tel||VARCHAR||Customer’s telephone number.|
|customer_email||VARCHAR||Unique, customer’s email.|
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
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
|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.|
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
|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
|total_name||VARCHAR||Sub-total, discount, tax, grand total, etc…|
That’s right – Tax rates, discounts, and service charges can change over time. Use this table to keep that information.
PART 3) PAYMENT
|payment_mode||VARCHAR||Up to you to decide – Cash, credit card, online banking, voucher, etc…|
|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.
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
LINKS & REFERENCES
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!