每天睡觉前必做两件事

数据库表的设计

一对多或多对一:多的一方加外键,描述数据的关系

多对多:加中间表来描述关系

一对多或多对一的对象存到数据库时,表的设计方案(部门和员工)

create table department ( id int PRimary key, name varchar( 40) ); create table employee ( id int primary key, name varchar( 40), salary decimal( 8,2), department_id int, constraint department_id_FK foreign key( department_id) references department(id) );

多对多对象的表的设计( 老师和学生)

create table teacher ( id int primary key, name varchar( 40), salary decimal( 8,2) ); create table student ( id int primary key, name varchar( 40) ); create table teacher_student ( teacher_id int, student_id int, primary key( teacher_id,student_id), constraint teacher_id_FK foreign key( teacher_id) references teacher( id), constraint student_id_FK foreign key( student_id) references student( id) );

一对一表的设计( 人和身份证)

主从关系,从加外键,外键约束,唯一约束,非空约束 create table person ( id int primary key, name varchar( 40) ); create table idcard ( id int primary key, city varchar( 40), constraint id_FK foreign key(id) references person(id) );

自连接表的设计( 家族管理系统)

create table person ( id int primary key, name varchar( 40), parent_id int, constraint parent_id_Fk foreign key( parent_id) references person( id) );