每天睡覺前必做兩件事

數據庫表的設計

一對多或多對一:多的一方加外鍵,描述數據的關系

多對多:加中間表來描述關系

一對多或多對一的對象存到數據庫時,表的設計方案(部門和員工)

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) );