Courier Management System ERD (With Database Structure)

Last Updated: November 10, 2023

Welcome to a sharing of an ERD for a courier management system. So you have picked up a project to manage courier deliveries? Or maybe you are stuck with this as a school project? Well, here is a sharing of my own simple version – Read on!

 

 

TABLE OF CONTENTS

 

ER DIAGRAM

First, here is the entire ER diagram and a quick overview of the system.

 

COURIER MANAGEMENT SYSTEM ERD

Courier Management System ERD (click to enlarge)

To keep things simple, I have grouped this system into 3 sections.

  • Users – The internal staff members, and parties related to the shipments.
  • Shipments – Self-explanatory.
  • Vehicles – To send/receive the packages.

 

FOR REFERENCE ONLY

My developer senses are tingling, I can feel the keyboard warriors doing their usual battle cry. “This is not accurate”, “dumb, worst, stupid”, “not good”. By any means, this is only a simple example for your reference.

  • If you want to trace monetary transactions, go ahead and add another table on your own.
  • If you want to track packages, or one shipment can have multiple packages – Go ahead and do your own modifications.
  • All shipments have at least one history entry called “received shipment request”. Change it if you don’t like this.
  • If you want to separate staff into “managers, couriers, staff” – Go ahead and do so.

Yes, nobody is stopping you from making “smart choices”. You are building your own system, you have to decide your own rules.

 

 

TABLE STRUCTURE

Now that we are done with the overview, let us get into more details on the entities or tables.

 

PART 1) USERS

1A) STAFF

COLUMN DATA TYPE NOTES
staff_id INTEGER Primary key.
staff_name VARCHAR Full name.
staff_email VARCHAR Email. Set this to unique to prevent duplicate registrations.
staff_tel VARCHAR Contact number.
staff_type VARCHAR Up to you to decide – “Admin”, “Manager”, “Courier”, etc…
staff_password VARCHAR Login password.

This should be pretty self-explanatory, the internal staff. Some people like to create multiple tables for admin, manager, courier, and staff – I say no. Unless all of them have different datasets, it will be wise to just stick with a single “staff type” field.

 

1B) SHIPMENT PARTIES

COLUMN DATA TYPE NOTES
party_id INTEGER Primary key.
shipment_id INTEGER Foreign key.
party_name VARCHAR The person or company name.
party_role VARCHAR The role of this person/company for this shipment – Shop, supplier, warehouse, escrow, etc…
party_tel VARCHAR Contact number.
party_address VARCHAR Street address, if any.

Take note, these are “external users” for each individual shipment. Yep, a shipment can be very complex these days with multiple parties. For example:

  • A customer purchases something online and engages in the service of an escrow.
  • There are multiple parties involved here – Online shop, supplier, escrow, insurance, customer, and maybe even another logistics company.

So instead of creating multiple “fixed fields” in the shipment table, it is better to create an entire “shipment parties” on its own.

 

 

PART 2) SHIPMENTS

2A) SHIPMENT

COLUMN DATA TYPE NOTES
ship_id INTEGER Primary key.
ship_start DATE TIME Shipment start date and time.
ship_end DATE TIME Shipment end date and time.
ship_from VARCHAR Pick up the package at this location.
ship_to VARCHAR Send the package to this location.
ship_status VARCHAR Up to you to decide – Pending, picked up, in the warehouse, delivering, delivered, canceled, lost, damaged, etc…

To address the elephant in the room. Where to pick up the packages, and where to deliver them to.

P.S. It will be good to add a “ship URL” field. This will allow customers or even other parties to check the status of the shipment.

 

2B) SHIPMENT HISTORY

COLUMN DATA TYPE NOTES
ship_id INTEGER Primary and foreign key.
history_date DATE TIME Date and time of the “package movement”.
history_status VARCHAR Up to you to decide. Same as “ship status” above.
history_notes VARCHAR Notes, if any.
vehicle_id VARCHAR The vehicle related to this entry, if any.
staff_id INTEGER The staff related to this entry, if any.
party_id INTEGER The party related to this entry, if any.

To keep track of the shipment progress, whatever that happens along the way.

 

 

PART 3) VEHICLES

COLUMN DATA TYPE NOTES
vehicle_id VARCHAR Primary key. Vehicle registration plate.
vehicle_name VARCHAR Vehicle name.
vehicle_description VARCHAR A short description of the vehicle.
vehicle_type VARCHAR Up to you to decide – Trucks, vans, motorcycles, or maybe even bicycles.

Finally, the vehicles used for shipping. I personally think this is more “asset management” than “courier management”. So yep, don’t dig too deep into this one.

 

EXTRAS

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

 

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!