Courier Management System ERD (With Database Structure)

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

 

 

COURIER MANAGEMENT SYSTEM ERD

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.

 

 

 

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.

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart
• PHP MYSQL Courier Management System – Code Boxx