How to build a MySQL table correctly?
Good time of day!
I am writing a small service for a school (React + PHP + MySQL). I will need to store a schedule for each class. The number of classes is unknown. I want the most simple structure.
So far, the simplest one that comes to mind is this:
- id - index
- Class name - text
- Monday - text - here all lessons separated by commas
- Tuesday - text - here separated by commas all lessons
- Wednesday - text - here separated by commas all lessons
- Thursday - text - here all lessons separated by commas
- Friday - text - here separated by commas all lessons
- Saturday - text - here all lessons separated by commas
- Sunday - text - here separated by commas all lessons
The advantage of this option is that the structure is very simple and flexible, minus the additional operation of parsing the string for lessons.
How would such a base be organized?MySQL Anonymous, Aug 7, 2020
- id - index
classes (id, number)
items (id, name)
teachers (id, name)
schedule (class_id, subject_id, teacher_id, week_day, lesson_number)
adjustments (class_id, subject_id, teacher_id, date, lesson_number)Anonymous
Considering three factors:
1. Your statement that the application is planned to be small
2. Such an application will be really small, you are unlikely to make it for the school №63 in Tyumen, where this year they took 26 classes, even the 1st!))
3. There are really only seven days of the week, there won't be less of them, there won't be more of them, I think it's stupid to create a table for the days of the week, then it's better to create a field of type ENUM for this.
TAKI: the table can remain flat and it's a fact.
All other arguments are against - you will not learn how to make joins and queries to several tables in one sitting, you will not care about the possibility of speeding up the request by giving part of the calculations to the php interpreter, etc.
Are there any selections to be made? Well, you can generally simplify.
Two fields, class and schedule. Schedule - store in json-representation, something like:
To place the code, please use CodePen or similar tool. Thanks you!