`

Identifying Relationship vs. Non-identifying Relationship

 
阅读更多

Identifying vs non-identifying has nothing to do with identity. Simply ask yourself can the child record exist without the parent? It has to do with whether the primary key of the child (can include more than one coumn) includes the foreign key of the parent. In the non-identifying relationship the child's primary key(PK) MUST NOT include the foreign key(FK).

Ask yourself this question

  • Can the foriegn key from the parent be included in the primary key of the child?

or asked in the opposite way

  • Can the child record exist without the parent record?

These are the same question, just put differently. If the FK must not be included in the PK meaning the child can exist without the parent, then it is non-identifying. Otherwise it is identifying. As noted in the comments more clearly: If a child can exist without the parent, then it is non-identifying.

One-to-one identifying relationship

Social security numbers fit nicely in to this category. SSNs cannot exist with out a person. The person_id would be the PK for the person table, including columns such as a name and address. (let's keep it simple). The social_security_number table would include the ssn column and the person_id column as a foreign key. Since this FK could be the PK for the social_security_number table it is an identifying relationship.

One-to-one non-identifying relationship

At a large office complex you might have an office table that includes the room numbers by floor and building number with a PK, and a separate employee table. The employee table (child) has a FK which is the office_id column from the office table PK. While each employee has only one office and (for this example) every office only has one employee this is a non-identifying relationship since offices can exist without employees, and employees can change offices or work in the field.

One-to-many relatoinships

One-to-many relationships can be categorized easily by asking the same question. 

Many-to-many relationships

Many-to-many relationships are always identifying relationships. This may seem counter intuitive, but bear with me. Take two tables libary and books, each library has many books, and a copy of each book exists in many libraries.

Here's what makes it and identifying relationship: In order to implement this you need a linking table with two columns which are the primary keys of each table. Call them the library_id column and the ISBN column. This new linking table has no separate primary key, but wait! The foreign keys become a multi-column primary key for the linking table since duplicate records in the linking table would be meaningless. The links cannot exist with out the parents; therefore, this is an identifying relationship. I know, yuck right?

Most of the time the type of relationship does not matter.

All that said, usually you don't have to worry about which you have. Just assign the proper primary and foreign keys to each table and the relationship will discover itself.

分享到:
评论

相关推荐

    ROSE数据库资料

    我们可以使用 Identifying Relationship 和 Non-identifying Relationship 两个工具来建立表之间的关系。 四、ROSE 数据库设计优点 1. 快速设计数据库 使用 ROSE 2003,我们可以快速设计和实现数据库,从而提高...

    Introduction_to_Optimum_Design.pdf

    - **Duality in Linear Programming:** Explores the relationship between primal and dual problems, showing how the solution of one can be derived from the other. ### Chapter 8: Numerical Methods for ...

    SSD7 选择题。Multiple-Choice

    What is an identifying owner in an ER model? (a) The entity upon which a weak entity's existence depends (b) The relationship that identifies a weak entity's owner (c) The entity upon which a ...

    SAP BW Tables

    **Purpose:** This table defines the relationship between process chains and events, enabling the execution of multiple events within a single process chain. **Use Cases:** - Automating complex ...

    python3.6.5参考手册 chm

    PEP 446: Newly Created File Descriptors Are Non-Inheritable Improvements to Codec Handling PEP 451: A ModuleSpec Type for the Import System Other Language Changes New Modules asyncio ensurepip ...

    用Rational-Rose来建立数据库表.doc

    在本文中,我们设置了三个表之间的关系,使用Identifying Relationship和Non-identifying Relationship两个工具建立主外键关系。 九、总结 本文通过使用Rational-Rose建立数据库表,展示了数据库设计的重要性和...

    statistica 全套教程包括数据挖掘

    This is particularly useful for identifying potential predictors or patterns within the data. The **t-Tests** (independent samples, dependent samples, single sample) are used to compare means ...

    Using CiviCRM.epub

    CiviCRM is a web-based, open source CRM system, designed specifically to meet the needs of advocacy, non-profit and non-governmental organizations. Elected officials, professional/trade associations, ...

    Erwin使用说明书

    - **非标识关系(Non-Identifying Relationship)**:实体1的键不是实体2的主键,但作为外键存在。 Erwin的工具栏提供了丰富的功能,如放大缩小模型视图、切换模型类型、编辑主题域、保存模型、打印、比较模型等,...

    数据库模型设计.pdf

    ERWIN中常见的概念包括实体(Entity)、实体类别(Sub-category)、识别关系(Identifying relationship)以及非识别关系(Non-identifying relationship)。实体是数据模型中的基础对象,它可以代表现实世界中的任何事物。...

    报销数据库设计.doc

    - **非识别关系**(Non-identifying relationship)允许父表数据删除,但只清除子表的外键引用,而非删除子表数据。 - **视图关系**可能是为了数据展示或权限控制而创建的虚拟表。 通过这样的数据库设计,报销...

    Database Processing Fundamentals, Design, and Implementation (12th Edition).rar

    Mixed Identifying and Nonidentifying Patterns The For-Use-By Pattern Recursive Patterns The Data Modeling Process The College Report The Department Report The Department/Major Report The Student ...

    software architecture复习资料(包含考点)

    software architecture复习资料(包含考点)外教课 大部分附有中文翻译 1.Please summarise the state of the art of current...Recognition that structure influences non-functional ‘qualities’ of a system [76]

    ISO/IEC 27005:2011-EN

    E2 Detailed information security risk assessment...-............. E22 Example2 Ranking of Threats by Measures of RisK.……… 51 E.2.1 Example 1 Matrix with predefined values 52 E.2.3 Example 3 ...

Global site tag (gtag.js) - Google Analytics