Shipping Management System Free Database Design Tutorial
Introduction
“Shipping Management System” is a software solution for freight and logistics companies. It provides the ability to manage inventory and order management easily.
The need for a shipping management system is paramount in any business that ships products. Often, large companies outsource their shipping needs to third-party organizations that specialize in logistics. This is because the entire process of handling the shipment process can be time-consuming. Shipping management systems are necessary to allow businesses to focus on other aspects of their business rather than the day-to-day operations of managing their shipments.
Why would you need a Shipping Management System?
A Shipping Management System is software that helps you manage the shipping part of your business. There are many reasons why many business owners are looking for ways to make their work more efficient or save money on overhead costs. A Shipping Management System can help you achieve these things.
Consider how much time it would take to make all of your shipping arrangements by hand! It’s not just arranging for pickup and delivery – there’s accounting, taxes, billing, customer service, and more to do to get the package in front of your customer when they expect it! With Shipping Management Systems’ tools, management is easy.
Database Tables
Let us now create the tables and their columns. Please watch the video for the complete tutorial.
tbl_customer – this table will store the information of the customers registered in the system. It has the following columns and fields:
- customer_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
- last_name – last name of the customer
- first_name- first name of the customer
- middle_name- middle name of the customer
- contact -contact number of the client/customer, preferably mobile or cellphone number.
- email_address – email address of the customer
- address – permanent address of the customer
- username – the desired username of the customer.
- password -bthe desired password of the client/customer
- account_status – the value of this column is 0 or 1, 0 means deactivated or inactive, 1 is activated or active.
Create SQL Statement – the statement below is used to create the tbl_customer, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_customer` ( `customer_id` int(11) NOT NULL, `last_name` varchar(30) NOT NULL, `first_name` varchar(30) NOT NULL, `middle_name` varchar(30) NOT NULL, `contact` varchar(15) NOT NULL, `email_address` varchar(50) NOT NULL, `address` varchar(200) NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL, `account_status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_shipping – the package’s shipping transactions will be stored in this table. The table has 8 columns.
- shipping_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
- reference_number – unique number given as reference of the shipment
- shipping_date – the date of the shipping transaction
- customer_id – a foreign key that points out to the customer
- amount – the amount paid for the shipping transaction
- status – the status of the shipping transaction, either completed or pending.
- delivery_personnel_id – this is a foreign key that points out to the delivery personnel
- proof_of_transaction – proof of the completed transaction, it can be a photo.
Create SQL Statement – the statement below is used to create the tbl_shipping, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_shipping` ( `shipping_id` int(11) NOT NULL, `reference_number` varchar(30) NOT NULL, `shipping_date` datetime NOT NULL, `customer_id` int(11) NOT NULL, `amount` float NOT NULL, `status` int(1) NOT NULL, `delivery_personnel_id` int(11) NOT NULL, `proof_of_transaction` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_shipping_details – this table will store shipping details of the system. Shipping details table has 6 fields.
- details_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
- shipping_id – this is a foreign key that points out to the shipping transaction
- item_name – the names of the items to be shipped
- item_details – specific details of the items to be delivered
- quantity – the number of items to be delivered
- amount – amount paid for the items to be delivered
Create SQL Statement – the statement below is used to create the tbl_shipping_details, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_shipping_details` ( `details_id` int(11) NOT NULL, `shipping_id` int(11) NOT NULL, `item_name` varchar(50) NOT NULL, `item_details` varchar(200) NOT NULL, `quantity` int(4) NOT NULL, `amount` float NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_delivery_personnel – the information of the registered delivery personnel in the system will be stored in this table. It consists of 10 columns.
- delivery_personnel_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
- last_name – the last name of the delivery personnel
- first_name – the first name of the delivery personnel
- middle_name- the middle name of the delivery personnel
- contact – contact number of the delivery personnel, preferably mobile or cellphone number.
- email_address – email address of the delivery personnel
- address – address of the delivery personnel
- username – the desired username of the delivery personnel, combined with the password to log in to the system.
- password – the desired password of the delivery personnel , combined with the username to log in to the system.
- account_status – the value of this column is 0 or 1, 0 means deactivated or inactive, 1 is activated or active.
Create SQL Statement – the statement below is used to create the tbl_delivery_personnel, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_delivery_personnel` ( `delivery_personnel_id` int(11) NOT NULL, `last_name` varchar(30) NOT NULL, `first_name` varchar(30) NOT NULL, `middle_name` varchar(30) NOT NULL, `contact` varchar(15) NOT NULL, `email_address` varchar(50) NOT NULL, `address` varchar(200) NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL, `account_status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_payment – the payments made using the system for the shipment are stored in this table. Payment table comprises of 6 fields and they are presented as follows:
- payment_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
- reference_number – unique number as a reference of the payment
- customer_id – this is a foreign key that points out to the customer
- payment_date – the date the payment was made
- amount- the amount paid
- payment_type – the type of payment used it can be through cash or credit card
Create SQL Statement – the statement below is used to create the tbl_payment, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_payment` ( `payment_id` int(11) NOT NULL, `reference_number` varchar(30) NOT NULL, `customer_id` int(11) NOT NULL, `payment_date` datetime NOT NULL, `amount` float NOT NULL, `payment_type` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_user – this table store the information of the users of the system. User table has 7 columns
- user_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
- username – the preferred username of the users. They will need this to log in to their account.
- password – the desired password of the users. This is combined with the username to log in to the system.
- complete_name – complete name of the users.
- address- address of the users.
- email – email address of the users.
- contact_number – contact number of the users, preferably mobile or cellphone number.
Create SQL Statement – the statement below is used to create the tbl_user, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_user` ( `user_id` int(11) NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL, `complete_name` varchar(100) NOT NULL, `address` varchar(200) NOT NULL, `contact_number` varchar(15) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Summary
In this article and video tutorial, we have presented the list of tables and their respective columns or fields for the project on the shipping management system. We hope that the content provided above will give you an idea to design and develop the said system effectively.
You may visit our Facebook page for more information, inquiries, and comments. Please subscribe also to our YouTube Channel to receive free capstone projects resources and computer programming tutorials.
Hire our team to do the project.
Related Topics and Articles:
Shipping and Logistic Management System Free Source code in Bootstrap
Online Tracking and Monitoring System of Cargo using QR Code
