數(shù)據(jù)庫(kù)表中一對(duì)多關(guān)系怎么設(shè)計(jì)？

Database Design（數(shù)據(jù)庫(kù)設(shè)計(jì)）

馬克-to-win：

(一對(duì)多：one-to-many)

1) teacher and student.

(teacher表:兩列id（主鍵）,name。
pupil表: 三列：id（主鍵）,name,tid(外鍵))




舉例： Teacher "qixy" has two students: liyaohua,fuwenlong. Teacher "huanglaosh" has two students: mashuai,jiaxiaohu.

create table pupil(id int not null,name char(10),tid int);

create table teacher(id int not null,name char(10));
INSERT INTO pupil (id,name,tid) VALUES(1,'liyaohua',1);
INSERT INTO pupil (id,name,tid) VALUES(2,'fuwenlong',1);
INSERT INTO pupil (id,name,tid) VALUES(3,'mashuai',2);
INSERT INTO pupil (id,name,tid) VALUES(4,'jiaxiaohu',2);

INSERT INTO teacher (id,name) VALUES(1,'qixy');
INSERT INTO teacher (id,name) VALUES(2,'huanglaosh');
select * from pupil,teacher where tid=teacher.id and teacher.name='qixy';

+----+-----------+------+----+------+
| id | name      | tid  | id | name |
+----+-----------+------+----+------+
|  1 | liyaohua  |    1 |  1 | qixy |
|  2 | fuwenlong |    1 |  1 | qixy |
+----+-----------+------+----+------+