Service Marketplace Database Project
About the Project
Technology made it easier for people to accomplish daily tasks and activities. In the conventional method, customers avail themselves of services by visiting the shop that offers their desired services personally. Service providers also do their transactions manually which is time-consuming and requires too much effort to complete. The current method is thought to be inefficient and ineffective in streamlining the whole process of providing and availing services.
The capstone project, “Services Marketplace System” is designed to serve as a centralized platform for marketing and inquiring about different services. The system will serve as a platform where different service providers and customers will have an automated transaction.
Database Tables
This article will provide you with an idea about the service marketplace system database design.
tbl_service_category – this table stores the information of the category of the services offered by the service marketplace. The said table has three columns.
- category_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).
- category_name – the name of the category
- description – additional information about the service category
Create SQL Statement – the statement below is used to create the tbl_service_category, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.
CREATE TABLE `tbl_service_category` ( `category_id` int(11) NOT NULL, `category_name` varchar(30) NOT NULL, `description` varchar(50) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_services – this table stores the information of the services offered by the marketplace and it has six database fields.
- service_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).
- category_id – this is a foreign key that points out to the category of the service
- service_name – the name of the service
- description – additional information about the services
- banner_image – this is used to upload the banner of services
- status – 0-inactive, 1-active
Create SQL Statement – the statement below is used to create the tbl_services, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.
CREATE TABLE `tbl_services` ( `service_id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `service_name` varchar(30) NOT NULL, `description` varchar(100) NOT NULL, `banner_image` text NOT NULL, `status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_customer – this table stores information of the customer in the system. Customer table of the project contains 12 columns.
- 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).
- lastname – the last name of the customer
- firstname – the first name of the customer
- middlename – the middle name of the customer
- gender – the gender of the customer
- contact – the contact number of the customer preferably mobile number
- email – the email address of the customer
- complete_address – the complete address of the customer
- profile_picture – this is used to upload profile pictures of the customer
- username – the desired username of the customer combined with the password to login to the system
- password – the preferred password of the customer combined with the username to login.
- account_status – 0-inactive, 1-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, `lastname` varchar(30) NOT NULL, `firstname` varchar(30) NOT NULL, `middlename` varchar(30) NOT NULL, `gender` int(1) NOT NULL, `contact` varchar(15) NOT NULL, `email` varchar(50) NOT NULL, `complete_address` text NOT NULL, `profile_picture` text NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL, `account_status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_service_provider – this table stores the information of the service providers in the system and it has 14 columns.
- provider_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).
- lastname – the last name of the service provider
- firstname – the first name of the service provider
- middlename – the middle name of the service provider
- gender – the gender of the services provider
- contact – the contact number of the service provider preferably mobile number
- email – the email address of the service provider
- complete_address – the complete address of the service provider
- profile_picture – this is used to upload profile picture of the service provider
- company – the name of the company of the service provider
- work_experience – work experience of the service provider
- average_rating – average rating of the service provider
- username – the preferred username of the service provider for his/her account
- password – the preferred password of the service provider for his/her account
Create SQL Statement – the statement below is used to create the tbl_service_provider, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.
CREATE TABLE `tbl_service_provider` ( `provider_id` int(11) NOT NULL, `lastname` varchar(30) NOT NULL, `firstname` varchar(30) NOT NULL, `middlename` varchar(30) NOT NULL, `gender` int(1) NOT NULL, `contact` varchar(15) NOT NULL, `email` varchar(50) NOT NULL, `complete_address` text NOT NULL, `profile_picture` text NOT NULL, `company` varchar(100) NOT NULL, `work_experience` text NOT NULL, `average_rating` float NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_service_posted – this table stores the information of the service posted in the system.
- post_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).
- provider_id – this is a foreign key that points out to the provider
- service_id – this is a foreign key that points out to the service
- description – additional information about the service
- rate – the rate for the service
- availability – the availability of the service
- contact – the contact number for the services
- email – the email address for the service posted
- status – 0-unpublished, 1-published
Create SQL Statement – the statement below is used to create the tbl_service_posted, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.
CREATE TABLE `tbl_service_posted` ( `post_id` int(11) NOT NULL, `provider_id` int(11) NOT NULL, `service_id` int(11) NOT NULL, `description` varchar(100) NOT NULL, `rate` float NOT NULL, `availability` int(1) NOT NULL, `contact` varchar(15) NOT NULL, `email` varchar(50) NOT NULL, `status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_service_transaction – this table stores the service transactions made using the system. Service transaction table has ten columns.
- transaction_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 – reference number for the transaction
- customer_id – this is a foreign key that points out to the customer
- provider_id – this is a foreign key that points out to the provider
- service__id – this is a foreign key that points out to the service
- amount – the amount paid for the service transaction
- status – 0-pending, 1-cancelled, 2-completed
- remarks – additional information about the service transaction
- rating – the rating for the service transaction
- date_of_transaction – date when the transaction was made
Create SQL Statement – the statement below is used to create the tbl_service_transaction, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.
CREATE TABLE `tbl_service_transaction` ( `transaction_id` int(11) NOT NULL, `reference_number` varchar(15) NOT NULL, `customer_id` int(11) NOT NULL, `provider_id` int(11) NOT NULL, `service_id` int(11) NOT NULL, `amount` float NOT NULL, `status` int(1) NOT NULL, `remarks` varchar(50) NOT NULL, `rating` int(1) NOT NULL, `date_of_transaction` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_payment – this table stores the payment details made by the customer and it has seven database fields.
- 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 – the reference number for the payment
- transaction_id – this is a foreign key that points out to the transaction
- paid_by – the name of the client who paid
- amount – the amount paid for the transaction
- date_of_payment – the date when the payment was made
- processed_by (admin_id) – this is a foreign key that points out to the admin who processed the payment
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` text NOT NULL, `transaction_id` int(11) NOT NULL, `paid_by` varchar(100) NOT NULL, `amount` float NOT NULL, `date_of_payment` date NOT NULL, `processed_by` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_admin – this table stores the information of the administrator account in the system. Admin table of the project has nine columns.
- admin_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).
- lastname – last name of the admin
- firstname – first name of the admin
- email_address – email address of the admin
- contact – the contact number of the admin
- access_level – 0-support, 1-full access
- username- the desired username of the admin for his/her account
- password – the desired password of the admin for his/her account
- account_status – 0-inactive, 1-active
Create SQL Statement – the statement below is used to create the tbl_admin, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.
CREATE TABLE `tbl_admin` ( `admin_id` int(11) NOT NULL, `lastname` varchar(30) NOT NULL, `firstname` varchar(30) NOT NULL, `email_address` varchar(50) NOT NULL, `contact` varchar(15) NOT NULL, `access_level` int(1) NOT NULL, `username` varchar(30) NOT NULL, `password` text NOT NULL, `account_status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_company_info – the information of the company are stored in this table and it has seven fields.
- company_name – the name of the company
- address – the address of the company
- email – the email address of the company
- website – the website of the company
- facebook_page – the facebook page of the company
- logo – the logo of the company
- commission_rate – commission rate of the company.
Create SQL Statement – the statement below is used to create the tbl_company_info, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.
CREATE TABLE `tbl_company_info` ( `company_name` varchar(100) NOT NULL, `address` text NOT NULL, `email` varchar(50) NOT NULL, `website` varchar(100) NOT NULL, `facebook_page` varchar(100) NOT NULL, `logo` text NOT NULL, `commission_rate` float NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Summary
Database design planning is one of the most important aspects of designing and developing a database. It helps organizations to optimize their data storage and processing by designing the database in such a way that it can be easily accessed, managed, and updated.
Database design planning helps organizations to avoid data redundancy, inconsistency, and errors. It also helps to ensure that the data is stored in a way that is efficient and accurate.
By following proper database design planning, organizations can save time and money on their data management processes. They can also ensure that their databases are robust and scalable enough to meet future demands.
With the information provided in this article, we hope that this will serve as a guide in your system development on the project related to service marketplace.
Please watch the video tutorial that will be posted on our YouTube Channel.
Readers are also interested in:
Marketplace Platform in Django Free Source code
NFT Marketplace System Build Using Django
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.
