8.
5) Database Design
Table - 1
Description :- To store admin details
Field name Data type Description Constrains
admin_id Int(20) Id for admin Primary key
username varchar(100) Username for admin Not null
password Varchar(100) Password for admin Not null
Table - 2
Description :- To store customer details
Field name Data type Description Constrains
customer_id Int(20) Id for customer Primary key
name varchar(100) Name for customer Not null
address varchar(100) Address for customer Not null
contact varchar(100) Contact number of customer Not null
email Varchar(100) Email for customer Not null
proof Blob Proof of customer Not null
username varchar(100) Username for customer Not null
password varchar(100) Password for customer Not null
Table - 3
Description:- To store vendor details
Field name Data type Description Constrains
vendor_id Int(20) Id for vendor Primary key
name varchar(100) Name for vendor Not null
address Varchar(100) Address for vendor Not null
contact varchar(100) Contact number of vendor Not null
email Varchar(100) Email for vendor Not null
license no Varchar(100) License number of vendor Not null
photo Blob Photo of vendor Not null
proof Blob Proof of vendor Not null
username Varchar(100) username for vendor Not null
password Varchar(100) password for vendor Not null
Table - 4
Description:- To store farmer details
Field name Data type Description Constrains
farmer_id Int(20) Id for farmer Primary key
Name varchar(100) Name for farmer Not null
Address Varchar(100) Address for farmer Not null
Email Varchar(100) Email for customer Not null
Contact varchar(100) Contact number of farmer Not null
Photo Blob Photo of farmer Not null
Proof Blob Proof of farmer Not null
society no Varchar(100) Society number of farmer Not null
Username Varchar(100) username for farmer Not null
Password Varchar(100) password for farmer Not null
Table - 5
Description:- To store product details
Field name Data type Description Constrains
product_id Int(20) Id for product Primary key
product_name Varchar(100) Name of product Not null
Description Varchar(100) Description about product Not null
Image Blob Image of product Not null
Amount Double Amount of milk Not null
product type_id Int(20) Product type Id for product Foreign key
Table - 6
Description:- To store product type details
Field name Data type Description Constrains
product type_id Int(20) Id for product type Primary key
Product type name Varchar(100) Name for product type Not null
Table - 7
Description:- To store dairy collection details
Field name Data type Description Constrains
dc_id Int(20) Id for dairy collection Primary key
dc_milk quantity status Double Quantity of milk Not null
dc_date Date Date for dairy collection Not null
dc_time Time Time for dairy collection Not null
contract_id Int(20) Id for contract Foreign key
Table - 8
Description:- To store contract details
Field name Data type Description Constrains
contract_id Int(20) Id for contract Primary key
contract_date from Date Date from contract Not null
contract_date to Date Date to contract Not null
contract_file Blob File of contract Not null
farmer_id Int(20) Id for farmer Foreign key
vendor_id Int(20) Id for vendor Foreign key
Table - 9
Description:- To store purchase product details
Field name Data type Description Constrains
purchase_id Int(20) Id for purchase product Primary key
purchase_qty Double Quantity of milk Not null
purchase_date Date Date for purchase product Not null
purchase_time Time Time for purchase product Not null
purchase_amount Double Amount of milk Not null
product_id Int(20) Id for product Foreign key
product type_id Int(20) Id for product type Foreign key
customer_id Int(20) Id for customer Foreign key
Table - 10
Description:- To store payment details
Field name Data type Description Constrains
payment_id Int(20) Id for payment Primary key
payment_date Date Date for payment Not null
payment_time time Time for payment Not null
dc_id Int(20) Id for dairy collection Foreign key
dc_milk quantity status Double Quantity of milk Foreign key
purchase_id Int(20) Id for purchase product Foreign key
purchase_amount Double Amount of milk Foreign key
vendor_id Int(20) Id for vendor Foreign key
farmer_id Int(20) Id for farmer Foreign key
customer_id Int(20) Id for customer Foreign key
Table - 11
Description:- To store report details
Field name Data type Description Constrains
report_id Int(20) Id for report Primary key
report_date Date Date for report Not null
report_time time Time for report Not null
description Varchar(100) Description of report Not null
vendor_id Int(20) Id for vendor Foreign key
admin_id Int(20) Id for admin Foreign key
purchase_id Int(20) Id for purchase product Foreign key
payment_id Int(20) Id for payment Foreign key
dc_id Int(20) Id for dairy collection Foreign key
