数据库设计：定义数据库中实体之间的关系

I want to design a database for my application, and I have a question about the way to define a relation between the entities.

我想为我的应用程序设计一个数据库,我对如何定义实体之间的关系有疑问。

I have 2 related entities which ID is compound. For example:

我有2个相关的实体,ID是复合的。例如:

an address entity which are identified by the properties [street number, house number, city, country], and a phone number (xx-yyyyyy)

由地产[街道号码,门牌号码,城市,国家]和电话号码(xx-yyyyyy)标识的地址实体

an entity which is identified by the properties [region number, the number].

由属性[区域编号,编号]标识的实体。

The relation is "For each address there are several phone numbers. I have two possible ways to define the tables:

关系是“对于每个地址,有几个电话号码。我有两种可能的方法来定义表格:

Tables that connected by all the ID fields
- Address → [street number, house number, city, country, ..., region number, the number]
- PhoneNumber → [region number, the number, ...]

通过所有ID字段连接的表地址→[街道号码,门牌号码,城市,国家,...,地区号码,号码] PhoneNumber→[地区号码,号码......]

Tables with an ID field (GUID) which connected by this field.
- Address → [ID, street number, house number, city, country, ...,]
- PhoneNumber → [ID, region number, the number, ..., AddressID]

具有通过此字段连接的ID字段(GUID)的表。地址→[ID,街道号码,门牌号码,城市,国家,......] PhoneNumber→[ID,地区号码,号码,......,地址ID]

In the first solution, the primary key will be all the fields that identifies the entity, and in the second soultion, the primary key will be only the ID field.

在第一个解决方案中,主键将是标识实体的所有字段,而在第二个解决方案中,主键将只是ID字段。

My question is - what is the better way? (by performance, maintanance, design, etc...)

我的问题是 - 更好的方法是什么? (按性能,维护,设计等......)

2 个解决方案

#1

Whether or not you have GUID the second option is easier to code and design. When you select data by joining two tables you'll only need the identifier columns indexed. The other way you need composite indexes for just this join.

您是否拥有GUID第二个选项更容易编码和设计。通过连接两个表来选择数据时,您只需要索引标识符列。另一种方法是仅需要此连接的复合索引。

Having unique primary keys in tables in a enterprise corporate system is a valuable practice in my experience. (from both dba and developer perspective)

根据我的经验,在企业公司系统中的表中具有唯一的主键是一种有价值的实践。 (从dba和开发人员的角度来看)

Designing database for performance is a complicated issue involving many criteria. Rather then go after the theory, if you have Oracle at hand play with your two approaches in practice and use explain plan tools. I mean create tables with your mentioned approaches, play with indexes and use explain plan. If I am not mistaken Oracle even gives you an idea about query performance by projection (i.e. by letting you estimate number of rows in tables). I'm not sure about Express Edition version has this support

为性能设计数据库是一个涉及许多标准的复杂问题。而不是追求理论,如果你有Oracle在实践中使用你的两种方法并使用解释计划工具。我的意思是使用您提到的方法创建表,使用索引并使用解释计划。如果我没有弄错,Oracle甚至会通过投影(即通过让您估计表中的行数)来了解查询性能。我不确定Express Edition版本是否有此支持

#2

The primary key will never be "all of the fields in a table". This is not how relational databases are supposed to work, so forget that approach.

主键永远不会是“表中的所有字段”。这不是关系数据库应该如何工作,所以忘记这种方法。

The key is a key, and data is data. Never ever mix the two up. If you mix them, you end up with a situation where you need to change the key and all records in the database that use it as soon as the data changes. Which is bad.

密钥是密钥,数据是数据。永远不要混淆两者。如果混合它们,最终会出现这样的情况:一旦数据发生变化,您需要更改密钥以及数据库中使用它的所有记录。这很糟糕。

The only proper way to design this (common) 1:n relationship is:

设计这个(常见的)1:n关系的唯一正确方法是:

Address               PhoneNumber
-------               -----------
 *ID         <---+     *ID
 StreetNumber    +---  AddressID
 HouseNumber           RegionNumber
 City                  TheNumber
 Country               ...
 ...

The * denote primary keys, typically an auto-incrementing number with a unique index on it.

*表示主键,通常是带有唯一索引的自动递增数字。

The arrow denotes a foreign key relationship.

箭头表示外键关系。

#1