The relationship in MySQL

Relationship

The relationship between entities and entities is reflected in the design of the final database table. Divide the relationship into three categories: one-to-one, two-to-many (many-to-a) and many-to-many.
All relationships refer to the relationship between a table and a table.

One

One record in a table must correspond to only one record in another table, and vice versa.

Student table: Name, gender, age, height, weight, marital status, hometown, home address, emergency contact

ID	name	Sex	Age	Height	Weight	Marriage	Weight	Hometown	Home Address	Emergency contact Person

The table is designed in this form to meet the requirements of which name, gender, age, height, weight belong to

Solution: Separate common and infrequently used information into two tables. Commonly used data, but marriage, hometown, address and contacts are not commonly used data, if each query is to query all data, infrequently used data will affect efficiency, not actually.

Common Information table

ID	name	Sex	Age	Height	Weight

Non-commonly used information table: to ensure that not commonly used information and common information must be able to correspond: Find a unique field to join together two tables.

ID	Marriage	Weight	Hometown	Home Address	Emergency contact Person

A record in a common table that matches only one record in a single, infrequently used table. Vice versa.

One-to-many

One record in a table can correspond to multiple records in another table, but conversely, one record of the other table can only correspond to one record of another table. This relationship is a one-to-many or many-to-

The relationship between mother and child
Mom table:

ID	name	Age

Kids Table:

ID	name	Age

Above relationship: A mother can find more than one record in a child's table, but a child's watch can only find a mother. is a typical one-to-many relationship

The above design: solves the actual table problem of the entity, but does not solve the relationship problem: the child cannot find the mother, the mother also cannot find the child.

Solution: Add a field to a table to find records for another table. You should add a field to your child's table to the MOM table: Because the child's table records can only match the records of one mom's table.

ID	name	Age

Kids Table:

ID	name	Age	Mom ID

Many-to-many

One record in a table can correspond to multiple records in another table, and vice versa.

Teacher Teaching: Teachers and students
Teacher Table:

t_id	name	Sex

Student table:

s_id	name	Sex

The above design scheme L realizes the design of the entity, but does not maintain the relationship of the entity.

Solution: No matter which table you add fields to, there is a problem: The field holds multiple data, and it is a field that is related to other tables and does not conform to the table design specification. Add a new table that specifically maintains the relationship between the two tables
Teacher Table:

t_id	name	Sex

Student table:

s_id	name	Sex

Intermediate Relationship Table

ID	t_id	s_id

Students find teachers: Find student ID –> Intermediate table find matching records –> teacher table match
Teacher looking for students: Find teacher ID –> Heavy Armor table find matching records –> student table matching

The relationship in MySQL