Gym Management System Database Design
The project entitled gym management system is a web based system that manages the member records that includes the personal information, payment history and schedule of workout and exercises.
This article will provide you with the list of database tables that you might use in the development of your own gym management system.
You are allowed to use our database design as long as you will give credit to the site, LIKE and SHARE of this article and our Facebook page is highly appreciated.
Database Design and List of Database Tables
tblinstructor (instructor_id, instructor_name, contact, address, email, user_id)
Description: one of the modules of the gym management system is the encoding of the different gym instructors; they serve as the teacher or trainer and prepare the different workout plan. The tblinstructor table will store their personal information such as the complete name, contact address and email.
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tblinstructor` ( `instructor_id` int(11) NOT NULL AUTO_INCREMENT, `instructor_name` varchar(30) NOT NULL, `contact` varchar(11) NOT NULL, `address` varchar(100) NOT NULL, `email` varchar(30) NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`instructor_id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
tblmember (member_id, member_id_no, member_name, address, contact, email, age, gender, joining_date, end_of_membership_date, membership_id, user_id)
Description: to avail the different workout plan of the gym, you need to register to become a member of the gym and can access the facilities available in the gym. The tblmember table includes the member name, address, contact, email, age and gender; the table also includes the date of membership and the expiration date of membership. It also keeps the record of the staff who processed the registration and membership as it shows in the list of entities or columns of the tblmember; it is represented by the user_id which is the foreign key that links to the tbluser table.
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tblmember` ( `member_id` int(11) NOT NULL AUTO_INCREMENT, `member_id_no` varchar(15) NOT NULL, `member_name` varchar(50) NOT NULL, `address` varchar(100) NOT NULL, `contact` varchar(11) NOT NULL, `email` varchar(30) NOT NULL, `age` int(3) NOT NULL, `gender` int(1) NOT NULL, `joining_date` date NOT NULL, `end_of_membership_date` date NOT NULL, `membership_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`member_id`), KEY `membership_id` (`membership_id`,`user_id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
tblmembershiptype (type_id, type_name, membership_period, membership_amount, signup_fee, user_id)
Description: the gym offers different types of membership with corresponding amount and expiration date. The records of the membership type will be stored in tblmembershiptype
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tblmembershiptype` ( `type_id` int(11) NOT NULL AUTO_INCREMENT, `type_name` varchar(15) NOT NULL, `membership_period` varchar(15) NOT NULL, `membership_amount` float NOT NULL, `signup_fee` float NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`type_id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
tblpayment (payment_id, member_id, amount, payment_time, payment_date, user_id)
Description: the database table that stores the payment of customers upon the registration and membership is the tblpayment. The table includes two foreign keys, one is the member_id that links to the tblmember and the other one is the user_id that represents the one who processed and received the payment.
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tblpayment` ( `payment_id` int(11) NOT NULL AUTO_INCREMENT, `member_id` int(11) NOT NULL, `amount` float NOT NULL, `payment_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `payment_date` date NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`payment_id`), KEY `member_id` (`member_id`,`user_id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
tblpromotionalmaterial (promotion_id, promotion_name, file_upload, user_id)
Description: part of the marketing strategy of the gym is to provide the audience a promotional campaign in a form of digital content such as videos and slideshows. The videos will be uploaded to the system and it will be stored in the tblpromotionalmaterial
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tblpromotionalmaterial` ( `promotion_id` int(11) NOT NULL AUTO_INCREMENT, `promotion_name` varchar(30) NOT NULL, `file_upload` blob NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`promotion_id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
tbluser (user_id, complete_name, contact, address, username, password)
Description: the gym has multiple staff in duty with shifting schedules, the staff can access the system and allowed to process transactions such as the member registration and receiving of payments from the members. Their information and credentials is stored in the tbluser database table.
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tbluser` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `complete_name` varchar(30) NOT NULL, `contact` varchar(11) NOT NULL, `address` varchar(100) NOT NULL, `username` varchar(30) NOT NULL, `password` varchar(30) NOT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
tblworkout (workout_id, workout_name, description)
Description: workout and exercises is also encoded and stored in the system, it is stored in the tblworkout table with three entities namely the workout_id which is the primary key, workout_name and the description of the workout or exercises.
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tblworkout` ( `workout_id` int(11) NOT NULL AUTO_INCREMENT, `workout_name` varchar(25) NOT NULL, `description` varchar(100) NOT NULL, PRIMARY KEY (`workout_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
tblworkoutplan (plan_id, member_id, workout_id, work_out_time, work_out_date, instructor_id)
Description: the members are allowed to select the type of workouts that they want; the selection of the member will be stored in the tblworkoutplan, it will serve as a schedule of activities of the members. The table has 6 entities; plan_id is the primary key, member_id is the foreign key that links to the tblmember, workout_id is another foreign key that links to the tblworkout table, next is the schedule of workout (time and date), lastly the instructor that will guide in the conduct of exercise.
Create Table SQL Statement:
CREATE TABLE IF NOT EXISTS `tblworkoutplan` ( `plan_id` int(11) NOT NULL AUTO_INCREMENT, `member_id` int(11) NOT NULL, `workout_id` int(11) NOT NULL, `work_out_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `work_out_date` date NOT NULL, `instructor_id` int(11) NOT NULL, PRIMARY KEY (`plan_id`), KEY `member_id` (`member_id`,`workout_id`,`instructor_id`), KEY `instructor_id` (`instructor_id`), KEY `workout_id` (`workout_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Constraints for dumped tables
—
— Constraints for table `tblinstructor`
—
ALTER TABLE `tblinstructor`
ADD CONSTRAINT `tblinstructor_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON UPDATE CASCADE;
—
— Constraints for table `tblmember`
—
ALTER TABLE `tblmember`
ADD CONSTRAINT `tblmember_ibfk_2` FOREIGN KEY (`membership_id`) REFERENCES `tblmembershiptype` (`type_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `tblmember_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON UPDATE CASCADE;
—
— Constraints for table `tblmembershiptype`
—
ALTER TABLE `tblmembershiptype`
ADD CONSTRAINT `tblmembershiptype_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON UPDATE CASCADE;
—
— Constraints for table `tblpayment`
—
ALTER TABLE `tblpayment`
ADD CONSTRAINT `tblpayment_ibfk_2` FOREIGN KEY (`member_id`) REFERENCES `tblmember` (`member_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `tblpayment_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON UPDATE CASCADE;
—
— Constraints for table `tblpromotionalmaterial`
—
ALTER TABLE `tblpromotionalmaterial`
ADD CONSTRAINT `tblpromotionalmaterial_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON UPDATE CASCADE;
—
— Constraints for table `tblworkoutplan`
—
ALTER TABLE `tblworkoutplan`
ADD CONSTRAINT `tblworkoutplan_ibfk_3` FOREIGN KEY (`workout_id`) REFERENCES `tblworkout` (`workout_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `tblworkoutplan_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `tblmember` (`member_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `tblworkoutplan_ibfk_2` FOREIGN KEY (`instructor_id`) REFERENCES `tblinstructor` (`instructor_id`) ON DELETE NO ACTION ON UPDATE CASCADE;
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.
