规范化数据库模式中是否可以有多个外键？ [英] Is it possible to have more than one foreign key in a normalised database schema?
问题描述
地址（地址ID ，门牌号，邮政编码，道路名称，城市，县）
预订（预订ID ，房间号， leadGuestID，guest2ID，guest3ID ，
&arrivalDate，departmentDate， addressID ，amountOutstanding）
Guest（ guestID ，名字，姓氏，adultFlag，电话号码，地址ID ，电子邮件地址）
房间（ roomNo ， roomTypeName ）
RoomType（ roomTypeName ，楼层，basePrice，extraAdultPrice，extraChildPrice）
Address (addressID, houseNumber, postcode, roadName, city, county)
Reservation (reservationID, roomNo, leadGuestID, guest2ID, guest3ID,
arrivalDate, departureDate, addressID, amountOutstanding)
Guest (guestID, firstName, lastName, adultFlag, telephoneNo, addressID, emailAddress)
Room (roomNo, roomTypeName)
RoomType (roomTypeName, floor, basePrice, extraAdultPrice, extraChildPrice)
以上架构应该是标准化的4NF。但我看不到 leadGuestID，guest2ID& guest3ID 可以是外键，因为它们在任何其他表中都不作为主键存在。
The above schema is supposed to be normalised 4NF. But I cannot see how leadGuestID, guest2ID & guest3ID can be foreign keys, since they do not exist as primary keys in any other table. There are probably other issues here as well.
推荐答案
外键不必引用主键（它们可以引用任何唯一的键）。键，并且在某些DBMS中可以引用任何索引列），并且不必具有与引用列相同的名称。
Foreign keys don't have to reference primary keys (they can reference any unique key, and in some DBMSs can reference any indexed columns) and don't have to have the same name as the referenced columns.
列名不能很好地替代两个逻辑概念-域和角色。域是指在列中有效的一组值。作用是指专栏的含义或目的。例如，在（经理，下属）关系中，两列的域都是有效的员工标识符集，而角色则指示谁向谁报告。通常，当关系中只有一个域出现时，就无需指明角色。
Column names are a poor substitute for two logical concepts - domain and role. Domain refers to the set of values that are valid in a column. Role refers to the meaning or purpose of the column. For example, in a (manager, subordinate) relationship, the domain of both columns would be the set of valid employee identifiers, while the roles indicate who reports to who. Generally, when there is only one occurrence of a domain in a relation, there's no need to indicate the role.
规范化的目的是消除冗余的功能和联接依赖性这会带来数据不一致的风险。您必须先确定并列出功能和联接依赖关系，然后才能确定架构中的当前常规格式或问题。无法通过列名来完成。对数据模型的业务领域的了解可以使建模人员迅速发现可能的违规行为，但是即使是经验丰富的建模人员如果不列出依赖项，也会犯错，因此，如果您担心模式，则从那里开始。
The purpose of normalization is to eliminate redundant functional and join dependencies that create risks of inconsistent data. Before you can identify the current normal form or problems in a schema, it's necessary to determine and list the functional and join dependencies. It can't be done from column names. An understanding of the business domain of the data model may enable modelers to see possible violations quickly, but even experienced modelers make mistakes if they don't list the dependencies, so start there if you're concerned about the schema.
即使是最高标准形式的关系，也可能有多个外键。 SQL中的外键约束只是加强域完整性的一种方式，它们不会以任何方式影响依赖关系和/或规范化。
It's possible to have more than one foreign key in a relation even in the highest normal forms. Foreign key constraints in SQL are only a way of enforcing domain integrity, they don't affect dependencies and/or normalization in any way.
请注意，我并不是说 leadGuestID，guest2ID，guest3ID 是很好的设计。客人和预订之间的关系可能应该记录在一个单独的表中，但是这种设计不会违反前4个正常形式。
Note that I'm not saying leadGuestID, guest2ID, guest3ID is good design. The relationship between guests and reservations should probably be recorded in a separate table, but such designs don't violate the first 4 normal forms.
这篇关于规范化数据库模式中是否可以有多个外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
