DB schema for a booking system of fitness class

Question

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.

Answer 1

Student: ID, Name, ...
Class: ID, Name, MaxStudents, ...
Student_in_Class: STUDENT_ID, CLASS_ID, DATE_ENROLL

Answer 2

*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 - stores the classes each student is registered for

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")

• put a unique constraint index on class_id and student_id to prevent duplicates

Class - stores your classes

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 - stores your students

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")

• put a unique constraint index on fname, lname, and date of birth to eliminate duplicates (you may have two John Smiths, but two John Smiths w/ exact same birth date in same database is unlikely unless it's a very large database. Otherwise, consider using first name, last name, and phone as a unique constraint)