我有一桌人。每个人都拥有一项财产,并且许多人可能拥有某种财产。所以这是一个多对多的关系。这是架构:
CREATE TABLE persons (
person_id int(11) NOT NULL AUTO_INCREMENT,
firstname varchar(30) NOT NULL,
lastname varchar(30) NOT NULL,
PRIMARY KEY (person_id)
);
CREATE TABLE properties (
property_id int(11) NOT NULL AUTO_INCREMENT,
property varchar(254) NOT NULL UNIQUE,
PRIMARY KEY (property_id)
);
CREATE TABLE has_property (
person_id int(11) NOT NULL,
property_id int(11) NOT NULL,
PRIMARY KEY (person_id,property_id),
FOREIGN KEY (person_id) REFERENCES persons (person_id),
FOREIGN KEY (property_id) REFERENCES properties (property_id)
);
现在假设我想将这个人插入数据库:
- 名字:“约翰”
- 姓氏:“多伊”
- 属性:'property_A','property_B','property_C'
persons
+-----------+-----------+----------+
| person_id | firstname | lastname |
+-----------+-----------+----------+
| 1 | John | Doe |
+-----------+-----------+----------+
特性
+-------------+------------+
| property_id | property |
+-------------+------------+
| 1 | property_A |
| 2 | property_B |
| 3 | property_C |
+-------------+------------+
有属性
+-----------+-------------+
| person_id | property_id |
+-----------+-------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
+-----------+-------------+
到目前为止,我想到的最好的事情是在 person 表中进行定期插入:
INSERT INTO persons (firstname,lastname) VALUES ('John','Doe');
然后进行选择以查找我刚刚插入的人的 id
SELECT person_id FROM persons WHERE firstname='John' AND lastname='Doe';
为了插入其他两个表(因为我需要知道 person_id)。
但我认为一定有更好的方法,不是吗?