Database design (three relationships between tables)

Keywords: SQL Database Java Javascript

There are generally three relationships between tables: one-to-one, one-to-many and many-to-many.
The following three relationships explain the thinking and thinking process of database design.

(1) One-to-one relationship
For example, the following table holds information about people, men and women, and requires that all couples be investigated and punished.
sql code:

CREATE TABLE IF NOT EXISTS person(
    id INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(10),
    sex CHAR(1),
    husband INT,
    wife INT 
);
INSERT INTO person VALUES(1,'Floret','0',3,0);
INSERT INTO person VALUES(2,'Xiao Ming','1',0,4);
INSERT INTO person VALUES(3,'Zhang San','1',0,1);
INSERT INTO person VALUES(4,'Xiaoli','0',2,0);
INSERT INTO person VALUES(5,'Wang Wu','1',0,0);

The person table corresponding to the sql code:

As can be seen from the table, Xiaohua and Zhangsan are husband and wife, Xiaoming and Xiaoli are husband and wife. Usually, in order to query conveniently, two tables are needed, but in order to save space in actual projects, only one table is usually built. To realize one-to-one query, two views (virtual tables) can be built.
* It is a small detail to mention that view is only a logical concept and does not occupy physical memory independently. It is attached to the data table, and the corresponding table data will be changed when the view is modified.*

CREATE VIEW men AS (SELECT * FROM person WHERE sex='1');
CREATE VIEW women AS (SELECT * FROM person WHERE sex='0');

men view:

women View:

Execute queries:
Mode 1 (original method): SELECT men. sname AS husband, women. sname AS wife FROMmen, women WHERE men. id = women. husband and women. id = men. wife;

Mode 2 (connection method): SELECT men.sname AS husband,women.sname AS wife FROM men INNER JOIN women ON men.id = women.husband AND women.id = men.wife;

2. One-to-many relationship
For example, a person can own more than one car and ask for all the cars that a person owns.
Analysis: In fact, this situation can also use a table, but because a person can own more than one car, if a table is used, it will cause excessive redundant information. A good way to design a table is to build tables separately for people and vehicles. How do you associate the two tables? There is a clever way to add a foreign key field (person number) to the vehicle list.
* (Summary of ideas:'Build two tables, one side does not move, and the other side adds a foreign key field.)*

sql code:

    //Establishment of staffing table
CREATE TABLE human(
    id VARCHAR(12) PRIMARY KEY,
    sname VARCHAR(12),
    age INT,
    sex CHAR(1)
);
INSERT INTO human VALUES('H001','Xiao Wang',27,'1');
INSERT INTO human VALUES('H002','Xiao Ming',24,'1');
INSERT INTO human VALUES('H003','Zhang Hui',28,'0');
INSERT INTO human VALUES('H004','Li Xiaoyan',35,'0');
INSERT INTO human VALUES('H005','Von Dannor',29,'1');
INSERT INTO human VALUES('H006','Zhou Qiang',36,'1');
    //Establishment of Vehicle Information Table
CREATE TABLE car(
    id VARCHAR(12) PRIMARY KEY,
    mark VARCHAR(24),
    price NUMERIC(6,2),
    hid VARCHAR(12),
    CONSTRAINT fk_human FOREIGN KEY(hid) REFERENCES human(id)
);
INSERT INTO car VALUES('C001','BMW',65.99,'H001');
INSERT INTO car VALUES('C002','BenZ',75.99,'H002');
INSERT INTO car VALUES('C003','Skoda',23.99,'H001');
INSERT INTO car VALUES('C004','Peugeot',20.99,'H003');
INSERT INTO car VALUES('C005','Porsche',295.99,'H004');
INSERT INTO car VALUES('C006','Honda',24.99,'H005');
INSERT INTO car VALUES('C007','Toyota',27.99,'H006');
INSERT INTO car VALUES('C008','Kia',18.99,'H002');
INSERT INTO car VALUES('C009','Bentley',309.99,'H005');

Personnel table corresponding to sql code:

The vehicle information table corresponding to sql code:

Execute queries: SELECT human.sname AS owner, car.mark AS vehicle FROM human,car WHERE human.id = car.hid;

` SELECT human.sname AS owner, car.mark AS vehicle FROM human INNER JOIN car WHERE human.id = car.hid;

3. Multi-to-Many Relations`
For example: students choose courses, one student can take many courses, each course can be chosen by many students.
Analysis: This method can build tables in a similar one-to-many way, but there is too much redundant information. A good way is to separate entities and relationships and create tables separately. The entity tables are student tables and curriculum tables, and the relationship tables are elective tables. The relationship tables are constructed in the way of joint primary keys (composed of student tables and curriculum tables).

sql code:

    //Establishment of Student List
CREATE TABLE student(
    id VARCHAR(10) PRIMARY KEY,
    sname VARCHAR(12),
    age INT,
    sex CHAR(1),
    class VARCHAR(6)
);
INSERT INTO student VALUES('p0001','Wang Jun',20,1,'c101');
INSERT INTO student VALUES('p0002','Zhang Yu',21,1,'c101');
INSERT INTO student VALUES('p0003','Liu Fei',22,1,'c102');
INSERT INTO student VALUES('p0004','Zhao Yan',18,0,'c103');
INSERT INTO student VALUES('p0005','Ting Zeng',19,0,'c103');
INSERT INTO student VALUES('p0006','Zhou Hui',21,0,'c104');
INSERT INTO student VALUES('p0007','Xiaohong',23,0,'c104');
INSERT INTO student VALUES('p0008','Yang Xiao',18,0,'c104');
INSERT INTO student VALUES('p0009','Li Jie',20,1,'c105');
INSERT INTO student VALUES('p0010','chief counsellor of Liu Bang',22,1,'c105');

    //Establishment of curriculum
CREATE TABLE course(
    id VARCHAR(10) PRIMARY KEY,
    sname VARCHAR(12),
    credit NUMERIC(2,1),
    teacher VARCHAR(12)
);
INSERT INTO course VALUES('C001','Java',3.5,'Miss Li');
INSERT INTO course VALUES('C002','Advanced mathematics',5.0,'Mr. Zhao');
INSERT INTO course VALUES('C003','JavaScript',3.5,'Teacher Wang');
INSERT INTO course VALUES('C004','discrete mathematics',3.5,'Mr. Bu');
INSERT INTO course VALUES('C005','data base',3.5,'Liao teacher');
INSERT INTO course VALUES('C006','operating system',3.5,'Mr. Zhang');

    //Establishment of elective list
CREATE TABLE sc(
    sid VARCHAR(10),
    cid VARCHAR(10)
);

ALTER TABLE sc ADD CONSTRAINT pk_sc PRIMARY KEY(sid,cid);
ALTER TABLE sc ADD CONSTRAINT fk_student FOREIGN KEY(sid) REFERENCES student(id);
ALTER TABLE sc ADD CONSTRAINT fk_course FOREIGN KEY(cid) REFERENCES course(id);

INSERT INTO sc VALUES('S0001','C001');
INSERT INTO sc VALUES('S0001','C002');
INSERT INTO sc VALUES('S0001','C003');
INSERT INTO sc VALUES('S0002','C001');
INSERT INTO sc VALUES('S0002','C004');
INSERT INTO sc VALUES('S0003','C002');
INSERT INTO sc VALUES('S0003','C005');
INSERT INTO sc VALUES('S0004','C003');
INSERT INTO sc VALUES('S0005','C001');
INSERT INTO sc VALUES('S0006','C004');
INSERT INTO sc VALUES('S0007','C002');
INSERT INTO sc VALUES('S0008','C003');
INSERT INTO sc VALUES('S0009','C001');
INSERT INTO sc VALUES('S0009','C005');

The student table corresponding to sql code:

Course schedule corresponding to sql code:

Course Selection Schedule Corresponding to sql Code:

Executing queries: (no information about students and no information about courses selected by students)
Ordinary way:

SELECT * FROM student WHERE student.id NOT IN (SELECT sid FROM sc);
SELECT * FROM course WHERE course.id NOT IN (SELECT cid FROM sc);

Relevance:

SELECT student.* FROM student LEFT JOIN sc ON student.id=sc.sid LEFT JOIN course ON course.id = sc.cid WHERE course.sname IS NULL;
SELECT course.* FROM course LEFT JOIN sc ON course.id=sc.cid LEFT JOIN student ON student.id = sc.sid WHERE student.sname IS NULL;

Posted by drkstr on Sun, 19 May 2019 20:36:53 -0700

Programmer Group

Database design (three relationships between tables)

Hot Keywords