MySQL table one-to-one/one-to-many/many-to-many links

1. Many-to-many association relationships in a database are generally handled in the form of intermediate tables, converting many-to-many to two-to-many.

2. Through the relationship of the table, to help us how to build a table, build a few tables.

A record of a pair of one by one tables must correspond only to one record in another table, and vice versa. Student table: Name, gender, age, height, weight, place of origin, home address, emergency contact name, gender, age, height, body weight is a common data, but the origin, address and contact person is not commonly used data if every query is to query all data, infrequently used data will affect efficiency, Not really the usual information table: ID (P), name, gender, age, height, weight not used information table: ID (P), hometown, home address, emergency contact solution: share the common and infrequently used information in storage, divided into two tables are not commonly used information tables and common information tables, Ensure that the non-common information table and the Common information table can correspond to: Find a unique field to connect two tables together. A record in a common table can always match only one record in one infrequently used table, and vice versa.

One record in a pair of tables can correspond to more than one record in another table, but in turn, a record of the other table can only correspond to one record of the first table, which is a relationship between one or more pairs of mothers and children: Mother, child two entity Mother table: ID (P), name, age, Sex Kids Table: ID (P), name, age, sex or above relationship: A mother can find multiple records in a child's table (or maybe one), but a child can only find a mother is a typical one-to-many relationship. But the above design: solves the problem of the design table of the entity, but does not solve the relationship problem, the child cannot find the mother, the mother can not find the child solution: Add a field to a table to find records in another table: adding a field to the child's table points to the Mother's table, Because the record of the child's table can only match the records of a Mother's table. Mother table: ID (P), name, age, gender child table: ID (P), name, age, gender, mother table ID (mother table primary key)

A record of (a) in a many-to-many pair of tables can correspond to multiple records in another table (B); At the same time, a record in B table can correspond to multiple records in a table. Teacher and Student teacher table t_id (p), Name, Gender student table s_id (p), name, gender above design scheme: Realize the design of the entity, But there's no relationship to the entity. A teacher teaches multiple students, and a student is taught a solution by multiple teachers: Add an intermediate relationship table teacher and student relationship table: ID (P), t_id,s_id Teacher table and intermediate table Form a one-to-many relationship, and the intermediate table is a multi-table ; maintains a relationship that can only find a table; the same student table is a one-to-many relationship with the middle table; Students find a teacher: Find the student ID---> intermediate table find Matching records (multiple)---> Teacher's Table Matching (a) teacher to find students: Find teacher ID---> intermediate table find Matching records (multiple)---> Student table matching (one)

MySQL table one-to-one/one-to-many/many-to-many links