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

 

ER DIAGRAM

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.

 

TABLE DETAILS

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

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.

 

 

EXTRA BITS & LINKS

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

 

INFOGRAPHIC CHEAT SHEET

Attendance Management System ERD (click to enlarge)

 

THE END

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!

Leave a Comment

Your email address will not be published. Required fields are marked *