问题在SQL中指定表

I am relatively new to database design. I have a database which has the following design

我对数据库设计比较陌生。我有一个具有以下设计的数据库

I have specified the the tables in SQL as follows.

我在SQL中指定了表格,如下所示。

CREATE TABLE Piece
(identifier INT NOT NULL Unique,
 value      INT NOT NULL,
 the date it was acquited DATE,
 the date it was made DATE,
 PRIMARY KEY (identifier));

CREATE TABLE Person
(name VARCHAR(50),
 person_id INT NOT NULL Unique,
 biography VARCHAR (50),
 date of birth DATE,
 date of death DATE,
 PRIMARY KEY (person_id));

CREATE TABLE Jewel
(code INT NOT NULL Unique,
 gem type VARCHAR (50),
 weight INT,
 quality VARCHAR (50),
 color VARCHAR (50),
 description VARCHAR (50),
 PRIMARY KEY (code));

CREATE TABLE Gem
(type VARCHAR (50) NOT NULL,
 hardness INT,
 density  INT,
 FOREIGN KEY (type) references JEWEL(gem type));

CREATE TABLE Ownership
(person VARCHAR (50),
 piece  INT,
 start of ownership DATE,
 end of ownership   DATE,
 FOREIGN KEY (person) references PERSON(person_id),
 FOREIGN KEY (piece) references PIECE(identifier));

My question is

我的问题是

1.) How can I specify a primary key to the GEM table as all the 3 attributes are not unique, should I have to create a new attribute like Gem_id, as I would prefer to use the existing attributes and not add a new attribute.

1.)如何指定GEM表的主键,因为所有3个属性都不是唯一的,我是否必须创建一个像Gem_id这样的新属性,因为我更喜欢使用现有属性而不添加新属性。

2.) I have used person_id attribute in the person table to make it unique and use it as a primary key, is there another way to create a primary key for the person table without adding the extra attribute and obviously I cant include constraints to the existing attribute and make it UNIQUE

2.)我在person表中使用了person_id属性使其唯一并将其用作主键,是否有另一种方法为person表创建主键而不添加额外属性,显然我不能包含约束现有属性并使其具有独特性

3.) Is all integrity constraints and data types right ? Is my design flawed in any way.

3.)所有完整性约束和数据类型是否正确?我的设计是否有任何缺陷。

1 个解决方案

#1

1) Yes you can create a multi column primary key (composite primary key) but it will be unique. Something like this should do it:

1)是的,您可以创建多列主键(复合主键),但它将是唯一的。这样的事情应该这样做:

CREATE TABLE Gem
(type VARCHAR (50) NOT NULL,
 hardness INT,
 density  INT,
 PRIMARY KEY (type , hardness, density ),
 FOREIGN KEY (type) references JEWEL(gem type));

2) There are other ways to create a primary key for the Person table but I would not recommend it. Person_id is what I would use here.

2)还有其他方法为Person表创建主键,但我不推荐它。 Person_id就是我在这里使用的。

3) I would make the following changes to your design:

3)我会对您的设计进行以下更改:

Make Person in the ownership table an int rather than a VARCHAR. If it's a foreign key to person_id, then you don't want it to be a VARCHAR.

将所有权表中的Person设为int而不是VARCHAR。如果它是person_id的外键,那么您不希望它是VARCHAR。

Add an int identifier to the Gem table. GemTypeId or something.

将一个int标识符添加到Gem表中。 GemTypeId或其他东西。

In the jewel table, replace the gem type column with your new GemTypeId column.

在宝石表中,将gem类型列替换为新的GemTypeId列。

There might be more, but these were the ones that jumped out at me.

可能会有更多,但这些是跳出来的。

It seems like you are trying not to use IDs to represent your data. Is there a reason for this? If you continue down this road you may run into data integrity issues.

您似乎在尝试不使用ID来表示数据。是否有一个原因?如果您继续沿着这条路走下去,可能会遇到数据完整性问题。

#1