英文:
Foreign key for subclasses in SQL
问题
我有宠物、顾客和送货公司发票之间的关系，宠物有三个子类别：狗、猫和鸟。您应该如何将其翻译成SQL？发票中的id应该引用什么？这个关系是多对多的关系。
-- 创建宠物表
CREATE TABLE Pets (
id INT NOT NULL,
BirthDate DATE,
Size INT,
Weight INT,
Price INT NOT NULL,
Location VARCHAR(30) NOT NULL,
Disability VARCHAR(50),
Breed VARCHAR(30),
Type VARCHAR(10), -- 添加一个类型字段以区分狗、猫和鸟
CONSTRAINT PETS_PK PRIMARY KEY (id)
)
-- 创建顾客表
CREATE TABLE Customers (
id INT NOT NULL,
-- 其他顾客信息列
CONSTRAINT CUSTOMERS_PK PRIMARY KEY (id)
)
-- 创建送货公司表
CREATE TABLE Shipping_Company (
id INT NOT NULL,
-- 其他送货公司信息列
CONSTRAINT SHIPPING_COMPANY_PK PRIMARY KEY (id)
)
-- 创建发票表
CREATE TABLE Invoice (
id INT NOT NULL,
C_id INT NOT NULL, -- 引用顾客的ID
P_id INT NOT NULL, -- 引用宠物的ID
S_id INT NOT NULL, -- 引用送货公司的ID
CONSTRAINT INV_PK PRIMARY KEY (id),
CONSTRAINT INV_CID_FK FOREIGN KEY (C_id) REFERENCES Customers (id),
CONSTRAINT INV_PID_FK FOREIGN KEY (P_id) REFERENCES Pets (id),
CONSTRAINT INV_SID_FK FOREIGN KEY (S_id) REFERENCES Shipping_Company (id)
)
上述SQL代码在宠物表中添加了一个类型字段（Type），以区分狗、猫和鸟。发票表中的P_id现在引用宠物的ID，这样可以在发票中记录关于宠物的信息。同时，宠物表中的id列作为主键，并与发票表中的P_id列建立了外键关系。
英文:
I have a relationship between Pets, Customers, and Shipping Company Invoice and Pets has three subclasses Dog, Cat, and Bird, how should I translate that into SQL, what would the id in Invoice reference? The relationship is a many to many relation.
CREATE TABLE Dog(
id INT NOT NULL,
BirthDate Date,
Size INT,
Weight INT,
Price INT NOT NULL,
Location VARCHAR(30) NOT NULL,
Disability VARCHAR(50),
Breed VARCHAR(30)
CONSTRAINT DOG_PK PRIMARY KEY (id)
)
CREATE TABLE Cat(
id INT NOT NULL,
BirthDate Date,
Size INT,
Weight INT,
Price INT NOT NULL,
Location VARCHAR(30) NOT NULL,
Disability VARCHAR(50),
Breed VARCHAR(30)
CONSTRAINT CAT_PK PRIMARY KEY (id)
)
CREATE TABLE Bird(
id INT NOT NULL,
BirthDate Date,
Size INT,
Weight INT,
Price INT NOT NULL,
Location VARCHAR(30) NOT NULL,
Disability VARCHAR(50),
Breed VARCHAR(30),
Color VARCHAR(30),
CONSTRAINT BIR_PK PRIMARY KEY (id)
)
CREATE TABLE Invoice(
C_id INT NOT NULL,
P_id INT NOT NULL,
S_id INT NOT NULL,
CONSTRAINT INV_PK PRIMARY KEY(C_id, P_id, S_id),
CONSTRAINT INV_CID_FK FOREIGN KEY (C_id) REFERENCES Customers(id),
CONSTRAINT INV_SID_FK FOREIGN KEY (S_id) REFERENCES Shipping_Company(id),
)
答案1
得分: 1
你需要一个代替你的狗、猫和鸟实体的动物实体，该实体具有一个种类（狗、猫、鸟、雪貂等）属性。当你尝试使用类继承时，SQL数据设计变得不合理复杂。
将你的狗、猫、鸟属性的并集放入你的动物实体中。你不必为每种动物使用每个属性，这就是NULL的用途。
英文:
You need an Animal entity with a Species (dog, cat, bird, ferret, etc) attribute in place of your Dog, Cat, and Bird entities. SQL data design gets unreasonably complex when you try to use class inheritance.
Put the union of your Dog, Cat, Bird attributes in your Animal entity. You don't have to use every attribute for every animal, that's what NULL is for.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论