Mysql learned a lot, listened to a lot, finally to the business, or do not know where to start, this is the last level from learning to enter the industry.

When designing a table, first know the three paradigms:

  • First normal form: requires a primary key and requires that each field be atomically nondivisible
  • Second normal form: requires all non-primary key fields to be completely dependent on the primary key, with no partial dependency
  • Third normal form: all non-primary key fields and primary key fields cannot have a passing dependency

I don’t know what I’m talking about. So forget it.

First design

For example, the most classic students, curriculum.

Someone designed it like this:

Student Id Name Gender Id Card No. Student No. Course 1 Course 2 Course 3 Teacher 1 Teacher 2 Teacher 3Copy the code

So each student learned which courses, a statement to find out.

What’s the problem?

If you want to change the structure of the table, there may be too many fields in the table.

Second design

Teacher form Teacher ID Name Sex ID Card Number Course ID1 Course ID2 Course ID3 Course Schedule Course ID Name Student Form Student ID Name Sex ID Card Number Student Number Course ID1 Course ID2 Course ID3Copy the code

This is not a full table update, and there are fewer fields, but it is still inconvenient to add courses.

Teacher table Teacher ID Name Gender ID ID ID Number Of Class Schedule Course ID Name Student Table Student ID Name Gender ID ID Number of Student ID Teaching Table Primary Key ID Teacher ID Course ID Primary Key ID Student ID Course IDCopy the code

This design is the paradigm design, don’t ask, just understand

Third design

If we want to check the students’ courses and teachers, we need to do this:

Select the schedule. Name teacher table. Name class list JOIN teaching table join teacher table join class scheduleCopy the code

If the query is very common, we can optimize it

Schedule Course ID Course name Name of the instructorCopy the code

The query

Select the schedule. Class name schedule. Teacher name class scheduleCopy the code

In this way, it is good to have two tables in a row. Although the teacher has changed the schedule to update all the classes, this situation is relatively rare in business and is acceptable, so it is called anti-paradigm.

conclusion

Of course, there are a lot of details in the design of the table, will be involved gradually, don’t want to eat a fat day, the first line.

conclusion

  • If something is wrong, you are welcome to correct it.
  • If you don’t understand anything, you are welcome to point out and I will add chestnuts.
  • If you feel OK, you can like it and let more people see it.

Related reading:

  • Table design is the final mountain to learn mysql
  • Learn MySQL’s first mountain – index
  • Learn about MySQL’s second mountain — locks and transactions
  • Why does MySQL use B+ trees?