Reputation: 32721
I need a schema for fitness class.
The booking system needs to store max-number of students it can take, number of students who booked to join the class, students ids, datetime etc.
A student table needs to store classes which he/she booked. But this may not need if I store students ids in class tables.
I am hoping to get some good ideas.
Thanks in advance.
Upvotes: 1
Views: 1623
Reputation: 3482
*Not a mySql guru, I typically deal w/ MS SQL, but I think you'll get the idea. You might need to dig a little in the mySql docs to find appropriate data types that match the ones I've suggested. Also, I only gave brief explanation for some types to clarify what they're for, since this is mySql and not MS SQL.
Class_Enrollment_ID INT IDENTITY PK ("identity is made specifically
to serve as an id and it's a field that the system will manage
on its own. It automatically gets updated when a new record is
created. I would try to find something similar in mySql")
Class_ID INT FK
Student_ID INT FK
Date_Time smalldatetime ("smalldatetime just stores the date as a
smaller range of years than datetime + time up to minutes")
Class_ID INT IDENTITY PK
Name VARCHAR('size') UNIQUE CONSTRAINT INDEX ("UNIQUE CONSTRAINT INDEX is
like a PK, but you can have more than one in a table")
Max_Enrollment INT ("unless you have a different max for different sessions
of the same class, then you only need to define max enrollment once per
class, so it belongs in the class table, not the Class_Enrollment table")
Student_ID INT IDENTITY PK
First_Name VARCHAR('size')
Last_Name VARCHAR('size')
Date_of_Birth smalldatetime ("smalldatetime can store just the date,
will automatically put 0's for the time, works fine")
Upvotes: 1
Reputation: 39773
Student: ID, Name, ...
Class: ID, Name, MaxStudents, ...
Student_in_Class: STUDENT_ID, CLASS_ID, DATE_ENROLL
Upvotes: 1