Dairy Farm Management System Database Design
The capstone project entitled “Dairy Farm Management System” is an agricultural database system that serves as an alternative or replace to the common and usual system of records management which is the manual method of pen and paper. The said system was designed to assist the farmers in managing their records and transactions in the management of their dairy farm. Specifically the system will help them in the following areas; (1) cow information, (2) vaccine and feed monitoring, and (3) sales on milk and meat.
This article will provide you with the list of tables and field/columns for every table in the design of database structure/schema of Dairy Farm Management system. The team will later provide a video tutorial on how to create the database in PHPMyAdmin.
This database design has 9 tables with their respective fields and columns as well as their relationships among each other.
tbl_cow – this table will store the information of the cow. The table has 8 columns and they are the following:
- cow_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).
- cow_control_number – the control number given by the farm manager to identify each and every cow.
- cow_image – profile image of the cow.
- gender – gender of the cow.
- cow_type_id – cow type, this is a foreign key that links to the cow type table.
- date_of_birth – birth date of the cow.
- status – this column has two values: 0 for sold, 1 for available.
- user_id – the user who encode/manage/update the cow information. It is a foreign key that connects to the user table.
Create SQL Statement – the statement below is used to create the tbl_cow, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_cow` ( `cow_id` int(11) NOT NULL, `cow_control_number` varchar(15) NOT NULL, `cow_image` blob NOT NULL, `gender` int(1) NOT NULL, `cow_type_id` int(11) NOT NULL, `date_of_birth` date NOT NULL, `status` int(1) NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
tbl_cow_category – the list of cow category will be stored in this table. The cow category table has 3 columns.
- cow_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).
- cow_category_name – category name of the cow.
- description – other important information of the cow category.
Create SQL Statement – the statement below is used to create the tbl_cow_category, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_cow_category` ( `cow_category_id` int(11) NOT NULL, `cow_category_name` varchar(15) NOT NULL, `description` varchar(50) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
tbl_vaccine_monitoring – this table will store the vaccination date of the cow and it has 5 columns.
- vaccine_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).
- date_recorded – vaccination date.
- cow_id – cow information, this is a foreign key that links to the cow information table.
- remarks – other important information of the vaccination.
- user_id – the user who encode/manage/update the vaccine information. It is a foreign key that connects to the user table.
Create SQL Statement – the statement below is used to create the tbl_vaccine_monitoring, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_vaccine_monitoring` ( `vaccine_id` int(11) NOT NULL, `date_recorded` date NOT NULL, `cow_id` int(11) NOT NULL, `remarks` varchar(50) NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
tbl_feed_monitoring – feed schedule and monitoring of the cow will be stored in this table. The table has 8 columns.
- feed_monitoring_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).
- date_recorded – date of feeding was recorded.
- cow_id – this refers to the cow information, this is a foreign key that links to the cow information table.
- remarks – other important information of the feeding.
- food_item – food given to the cow.
- quantity – amount of food given to the cow.
- feeding_time_schedule – this is the scheduled time the food should be given to the cow.
- user_id – the user who encode/manage/update the feed monitoring information. It is a foreign key that connects to the user table.
Create SQL Statement – the statement below is used to create the tbl_feed_monitoring, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_feed_monitoring` ( `feed_monitoring_id` int(11) NOT NULL, `date_recorded` date NOT NULL, `cow_id` int(11) NOT NULL, `remarks` varchar(50) NOT NULL, `food_item` varchar(30) NOT NULL, `quantity` float NOT NULL, `feeding_time_schedule` time NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
tbl_milk_collection – cow collected from the cow will be stored in this table. The milk collection table has 8 columns.
- milk_collection_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).
- collection_control_no – control number provided by the system for every milk collection record.
- cow_id – this refers to the cow information, this is a foreign key that links to the cow information table.
- date_recorded – this refers to the date the milk was collected.
- liter_collected – number of litters collected.
- price_per_liter – price of milk per liter.
- total – total is equal to price * liters collected. This column is optional since we can always derived this column from price_per_liter and liter_collected.
- user_id – the user who encode/manage/update the feed monitoring information. It is a foreign key that connects to the user table.
Create SQL Statement – the statement below is used to create the tbl_milk_collection, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_milk_collection` ( `milk_collection_id` int(11) NOT NULL, `collection_control_number` varchar(15) NOT NULL, `cow_id` int(11) NOT NULL, `date_recorded` date NOT NULL, `liter_collected` float NOT NULL, `price_per_liter` float NOT NULL, `total` float NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
tbl_milk_sale_collection – collected milk will be sold to the customers and this table will store the sale transaction of milk. Milk sale collection table has 8 columns.
- milk_sale_collection_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).
- sale_collection_control_no – control number provided by the system for every milk sale transaction.
- customer_name – name of customer who bought milk.
- liter_sold – number of liter sold to the customer.
- price_per_liter – price per liter.
- total – total is equal to price * liter sold. This column is optional since we can always derived this column from price_per_liter and liter_sold.
- date_recorded – date of sale transaction.
- user_id – the user who encode/manage/update the feed monitoring information. It is a foreign key that connects to the user table.
Create SQL Statement – the statement below is used to create the tbl_milk_sale_collection, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_milk_sale_collection` ( `milk_sale_collection_id` int(11) NOT NULL, `sale_collection_control_no` varchar(15) NOT NULL, `customer_name` varchar(30) NOT NULL, `liter_sold` float NOT NULL, `price_per_liter` float NOT NULL, `total` float NOT NULL, `date_recorded` date NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
tbl_cow_sale – aside from milk, cow itself is also available for sale. This table will store the cow sale information, and it has 10 columns.
- cow_sale_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).
- invoice_no – is a unique sequence of numbers that refers to a sale transaction.
- date_recorded – the date the cow was sold.
- cow_id – this refers to the cow information, this is a foreign key that links to the cow information table.
- amount – price of the cow was sold.
- customer_name – name of the customer.
- customer_contact – contact number of the customer.
- customer_email – email address of the customer.
- remarks – other important message or notes about the sale transaction.
- user_id – the user who encode/manage/update the feed monitoring information. It is a foreign key that connects to the user table.
Create SQL Statement – the statement below is used to create the tbl_cow_sale, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_cow_sale` ( `cow_sale_id` int(11) NOT NULL, `invoice_no` varchar(15) NOT NULL, `date_recorded` date NOT NULL, `cow_id` int(11) NOT NULL, `amount` float NOT NULL, `customer_name` varchar(30) NOT NULL, `customer_contact` varchar(15) NOT NULL, `customer_email` varchar(30) NOT NULL, `remarks` varchar(50) NOT NULL, `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
tbl_user – this table will store the information of personnel who can access the records of the system.
- 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 – username of the personnel used to login together with the password.
- password – password of the personnel used to login together with the username.
- avatar – this will hold the profile image of the user.
- fullname – the complete name of the personnel or user.
- contact – contact number of the personnel (mobile/cellphone number).
- email – email address of the personnel/user.
- user_category_id – the user group or category of the user.
- 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_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` varchar(30) NOT NULL, `avatar` blob NOT NULL, `fullname` varchar(50) NOT NULL, `contact` varchar(15) NOT NULL, `email` varchar(30) NOT NULL, `user_category_id` int(1) NOT NULL, `status` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tbl_user_group – this table store the information of the user group which includes the functions they can and can’t access in the system.
- user_group_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).
- group_name – name of the category or user group (this field will group the user based on their designation).
- description – information on what the user group is all about.
- allow_add – this column is to allow or prevent user from adding a record.
- allow_edit – this column is to allow or prevent user from editing or updating a record.
- allow_delete – this column is to allow or prevent user from removing or deleting a record.
- allow_print – this column is to allow or prevent user from printing a report.
- allow_import – this column is to allow or prevent user from importing records to the system.
- allow_export – this column is to allow or prevent user from exporting records from the system.
Create SQL Statement – the statement below is used to create the tbl_user_group, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.
CREATE TABLE `tbl_user_group` ( `user_group_id` int(11) NOT NULL, `group_name` varchar(30) NOT NULL, `description` varchar(50) NOT NULL, `allow_add` int(1) NOT NULL, `allow_edit` int(1) NOT NULL, `allow_delete` int(1) NOT NULL, `allow_print` int(1) NOT NULL, `allow_import` int(1) NOT NULL, `allow_export` int(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Indexes for dumped tables
-- -- Indexes for table `tbl_cow` -- ALTER TABLE `tbl_cow` ADD PRIMARY KEY (`cow_id`), ADD KEY `cow_type_id` (`cow_type_id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `tbl_cow_category` -- ALTER TABLE `tbl_cow_category` ADD PRIMARY KEY (`cow_category_id`); -- -- Indexes for table `tbl_cow_sale` -- ALTER TABLE `tbl_cow_sale` ADD PRIMARY KEY (`cow_sale_id`), ADD KEY `cow_id` (`cow_id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `tbl_feed_monitoring` -- ALTER TABLE `tbl_feed_monitoring` ADD PRIMARY KEY (`feed_monitoring_id`), ADD KEY `cow_id` (`cow_id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `tbl_milk_collection` -- ALTER TABLE `tbl_milk_collection` ADD PRIMARY KEY (`milk_collection_id`), ADD KEY `cow_id` (`cow_id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `tbl_milk_sale_collection` -- ALTER TABLE `tbl_milk_sale_collection` ADD PRIMARY KEY (`milk_sale_collection_id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `tbl_user` -- ALTER TABLE `tbl_user` ADD PRIMARY KEY (`user_id`), ADD KEY `user_category_id` (`user_category_id`); -- -- Indexes for table `tbl_user_group` -- ALTER TABLE `tbl_user_group` ADD PRIMARY KEY (`user_group_id`); -- -- Indexes for table `tbl_vaccine_monitoring` -- ALTER TABLE `tbl_vaccine_monitoring` ADD PRIMARY KEY (`vaccine_id`), ADD KEY `cow_id` (`cow_id`), ADD KEY `user_id` (`user_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `tbl_cow` -- ALTER TABLE `tbl_cow` MODIFY `cow_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_cow_category` -- ALTER TABLE `tbl_cow_category` MODIFY `cow_category_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_cow_sale` -- ALTER TABLE `tbl_cow_sale` MODIFY `cow_sale_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_feed_monitoring` -- ALTER TABLE `tbl_feed_monitoring` MODIFY `feed_monitoring_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_milk_collection` -- ALTER TABLE `tbl_milk_collection` MODIFY `milk_collection_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_milk_sale_collection` -- ALTER TABLE `tbl_milk_sale_collection` MODIFY `milk_sale_collection_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_user` -- ALTER TABLE `tbl_user` MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_user_group` -- ALTER TABLE `tbl_user_group` MODIFY `user_group_id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tbl_vaccine_monitoring` -- ALTER TABLE `tbl_vaccine_monitoring` MODIFY `vaccine_id` int(11) NOT NULL AUTO_INCREMENT;
Constraints for dumped tables
-- -- Constraints for table `tbl_cow` -- ALTER TABLE `tbl_cow` ADD CONSTRAINT `tbl_cow_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `tbl_cow_ibfk_2` FOREIGN KEY (`cow_type_id`) REFERENCES `tbl_cow_category` (`cow_category_id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `tbl_cow_sale` -- ALTER TABLE `tbl_cow_sale` ADD CONSTRAINT `tbl_cow_sale_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `tbl_cow_sale_ibfk_2` FOREIGN KEY (`cow_id`) REFERENCES `tbl_cow` (`cow_id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `tbl_feed_monitoring` -- ALTER TABLE `tbl_feed_monitoring` ADD CONSTRAINT `tbl_feed_monitoring_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `tbl_feed_monitoring_ibfk_2` FOREIGN KEY (`cow_id`) REFERENCES `tbl_cow` (`cow_id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `tbl_milk_collection` -- ALTER TABLE `tbl_milk_collection` ADD CONSTRAINT `tbl_milk_collection_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `tbl_milk_collection_ibfk_2` FOREIGN KEY (`cow_id`) REFERENCES `tbl_cow` (`cow_id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `tbl_milk_sale_collection` -- ALTER TABLE `tbl_milk_sale_collection` ADD CONSTRAINT `tbl_milk_sale_collection_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `tbl_user` -- ALTER TABLE `tbl_user` ADD CONSTRAINT `tbl_user_ibfk_1` FOREIGN KEY (`user_category_id`) REFERENCES `tbl_user_group` (`user_group_id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `tbl_vaccine_monitoring` -- ALTER TABLE `tbl_vaccine_monitoring` ADD CONSTRAINT `tbl_vaccine_monitoring_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `tbl_vaccine_monitoring_ibfk_2` FOREIGN KEY (`cow_id`) REFERENCES `tbl_cow` (`cow_id`) ON DELETE CASCADE ON UPDATE CASCADE; COMMIT;
Our team can modify the project based on your specific business requirements.
You may visit our facebook page for more information, inquiries and comments.
Hire our team to do the project.
