仍然困惑关于识别与非识别关系 [英] Still Confused About Identifying vs. Non-Identifying Relationships

问题描述

所以，我一直在阅读在我的数据库设计中识别与非识别关系，并且一些SO的答案似乎与我矛盾。以下是我要查看的两个问题：

1. 识别和非识别关系之间的区别


2. 关于识别或不识别关系的可疑决策

ol>



查看每个问题的最高答案，我似乎得到了两个不同的想法，即一个识别关系是什么。





第一个问题的回答说，识别关系描述子表中的行的存在取决于父表中的行的情况。给出的例子是作者可以写多本书（1对n关系），但是一本书在没有作者的情况下是不可能存在的。





但是，当我阅读对问题二的回答时，我很困惑，因为它说：如果一个孩子识别其父，它是一个识别关系。然后，答案会给出一些示例，例如社会安全号码（标识某人），但地址不是（因为许多人可以住在地址）。对我来说，这听起来更像是主键和非主键之间的决定。





我自己的直觉（和其他网站的额外研究）指向第一个问题及其反应是正确的。但是，我想在我继续前进之前验证，因为我不想学习错误，因为我正在努力理解数据库设计。提前感谢。

解决方案

识别关系的技术定义是孩子的外键是其主键的一部分。 / p>



  CREATE TABLE AuthoredBook（
 author_id INT NOT NULL，
 book_id INT NOT NULL，
 PRIMARY KEY（author_id，book_id），
 FOREIGN KEY（author_id）参考文献作者（author_id），
 FOREIGN KEY（book_id）REFERENCES图书（book_id）
）; 
  

book_id 是外键，但它也是主键中的一个列。因此，此表与引用表 Books 具有标识关系。同样，它与作者有识别关系。





对YouTube视频的评论与视频。 video_id 应该是注释表的主键的一部分。

  CREATE TABLE注释（
 video_id INT NOT NULL，
 user_id INT NOT NULL，
 comment_dt DATETIME NOT NULL，
 PRIMARY KEY（video_id，user_id，comment_dt），
 FOREIGN KEY（video_id）REFERENCES videos（video_id），
 FOREIGN KEY（user_id）REFERENCES Users（user_id）
） ; 
  

这可能很难理解这一点，因为现在这种常见的做法是只使用一个串行代理键而不是复合主键：

  CREATE TABLE注释（
 comment_id SERIAL PRIMARY KEY，
 video_id INT NOT b，
 user_id INT NOT NULL，
 comment_dt DATETIME NOT NULL，
 FOREIGN KEY（video_id）REFERENCES videos（video_id），
 FOREIGN KEY（user_id）REFERENCES Users 
）; 
  

这会掩盖表格有识别关系的情况。





我会不认为SSN代表识别关系。有些人存在，但没有SSN。其他人可以申请获得新的SSN。因此，SSN只是一个属性，不是个人主键的一部分。





---





/ p>






因此，如果我们使用代理键代替复合主键，那么在使用识别或非识别关系之间没有明显的区别？ / p>


我想是这样。我犹豫说是，因为我们没有通过使用代理键更改表之间的逻辑关系。也就是说，您仍然无法在不参考现有视频的情况下发表评论。但这只是意味着video_id必须是非NULL。对我来说，逻辑方面是关于识别关系的要点。





但是，还有一个物理方面来识别关系。这是因为外键列是主键的一部分（主键不一定是复合键，它可以是单个列，它既是Comments的主键，也是Videos表的外键，但这意味着每个视频只能存储一个评论。）





识别关系似乎只是为了实体关系图表的重要性，而这出现在GUI数据建模工具中。




So, I've been reading up on identifying vs. non-identifying relationships in my database design, and a number of the answers on SO seem contradicting to me. Here are the two questions I am looking at:

1. What's the Difference Between Identifying and Non-Identifying Relationships
2. Trouble Deciding on Identifying or Non-Identifying Relationship

Looking at the top answers from each question, I appear to get two different ideas of what an identifying relationship is.

The first question's response says that an identifying relationship "describes a situation in which the existence of a row in the child table depends on a row in the parent table." An example of this that is given is, "An author can write many books (1-to-n relationship), but a book cannot exist without an author." That makes sense to me.

However, when I read the response to question two, I get confused as it says, "if a child identifies its parent, it is an identifying relationship." The answer then goes on to give examples such as Social Security Number (is identifying of a Person), but an address is not (because many people can live at an address). To me, this sounds more like a case of the decision between primary key and non-primary key.

My own gut feeling (and additional research on other sites) points to the first question and its response being correct. However, I wanted to verify before I continued forward as I don't want to learn something wrong as I am working to understand database design. Thanks in advance.

解决方案

The technical definition of an identifying relationship is that a child's foreign key is part of its primary key.

CREATE TABLE AuthoredBook (
  author_id INT NOT NULL,
  book_id INT NOT NULL,
  PRIMARY KEY (author_id, book_id),
  FOREIGN KEY (author_id) REFERENCES Authors(author_id),
  FOREIGN KEY (book_id) REFERENCES Books(book_id)
);

See? book_id is a foreign key, but it's also one of the columns in the primary key. So this table has an identifying relationship with the referenced table Books. Likewise it has an identifying relationship with Authors.

A comment on a YouTube video has an identifying relationship with the respective video. The video_id should be part of the primary key of the Comments table.

CREATE TABLE Comments (
  video_id INT NOT NULL,
  user_id INT NOT NULL,
  comment_dt DATETIME NOT NULL,
  PRIMARY KEY (video_id, user_id, comment_dt),
  FOREIGN KEY (video_id) REFERENCES Videos(video_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

It may be hard to understand this because it's such common practice these days to use only a serial surrogate key instead of a compound primary key:

CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  video_id INT NOT NULL,
  user_id INT NOT NULL,
  comment_dt DATETIME NOT NULL,
  FOREIGN KEY (video_id) REFERENCES Videos(video_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

This can obscure cases where the tables have an identifying relationship.

I would not consider SSN to represent an identifying relationship. Some people exist but do not have an SSN. Other people may file to get a new SSN. So the SSN is really just an attribute, not part of the person's primary key.

---

Re comment from @Niels:

So if we use a surrogate key instead of a compound primary key, there is no notable difference between use identifying or non-identifying relationship ?

I suppose so. I hesitate to say yes, because we haven't changed the logical relationship between the tables by using a surrogate key. That is, you still can't make a Comment without referencing an existing Video. But that just means video_id must be NOT NULL. And the logical aspect is, to me, really the point about identifying relationships.

But there's a physical aspect of identifying relationships as well. And that's the fact that the foreign key column is part of the primary key (the primary key is not necessarily a composite key, it could be a single column which is both the primary key of Comments as well as the foreign key to the Videos table, but that would mean you can store only one comment per video).

Identifying relationships seem to be important only for the sake of entity-relationship diagramming, and this comes up in GUI data modeling tools.

这篇关于仍然困惑关于识别与非识别关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！