当前位置：文江博客话题详情

数据库设计列命名约定；主键和外键

发布于 2024-12-29 08:36:04 字数 105 浏览 1 评论 0原文

按照惯例，我这么问是因为我不太确定；

通常在列命名中； id_* 指的是 pk，*id 指的是 fk？那么多个 id* 是否意味着 id_* 形成多列 pk？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

揪着可爱 2025-01-05 08:36:04

名为 FOO_BAR_ID 的字段会向我建议某种桥或映射表，而不是与复合主键的关系，这就是我相信您所建议的，下面是我倾向于如何命名的演示字段，我个人避免使用复合主键，因为它们打破了第二范式，而且我从未见过使用它们的逻辑合理性

  + a normal table
-----------------------------------------------------
| DEPARTMENT_SID | NAME | DESCRIPTION | ADDRESS_SID |
-----------------------------------------------------
  ^ pk                                  ^ fk

  + another normal table
--------------------------------------------------
| ADDRESS_SID | NUMBER | STREET | TOWN | POSTCODE |
--------------------------------------------------
  ^ pk

  + yet another normal table
-----------------------------------------------------------------------
| EMPLOYEE_SID | FIRST_NAME | LAST_NAME | DATE_OF_BIRTH | ADDRESS_SID |
-----------------------------------------------------------------------
  ^ pk                                                    ^ fk

  + bridge table as an employee can belong to many departments
  + uses a composite key rather then defining a single primary key
---------------------------------
! EMPLOYEE_SID ! DEPARTMENT_SID |  
---------------------------------
  ^ cpk / fk     ^ cpk / fk

  + bridge table defining a single primary key
----------------------------------------------------------- 
| EMPLOYEE_DEPARTMENT_SID ! EMPLOYEE_SID | DEPARTMENT_SID |
-----------------------------------------------------------
  ^ pk                      ^ fk           ^ fk

  + a table with a foreign key to the bridge table
------------------------------------------------------
! SHIFT_SID | EMPLOYEE_DEPARTMENT_SID | HOURS_WORKED |
------------------------------------------------------
  ^ pk        ^ fk                 

pk = PRIMARY KEY, fk = FOREIGN KEY, cpk = COMPOSITE PRIMARY KEY

A field named FOO_BAR_ID would suggest to me some sort of bridge or map table, not a relationship to a composite primary key which is what I believe you are suggesting, below is a demonstration of how I tend to name fields, personally I avoid using composite primary keys because they break second normal form and tbh I have never seen a logical rational for using them

  + a normal table
-----------------------------------------------------
| DEPARTMENT_SID | NAME | DESCRIPTION | ADDRESS_SID |
-----------------------------------------------------
  ^ pk                                  ^ fk

  + another normal table
--------------------------------------------------
| ADDRESS_SID | NUMBER | STREET | TOWN | POSTCODE |
--------------------------------------------------
  ^ pk

  + yet another normal table
-----------------------------------------------------------------------
| EMPLOYEE_SID | FIRST_NAME | LAST_NAME | DATE_OF_BIRTH | ADDRESS_SID |
-----------------------------------------------------------------------
  ^ pk                                                    ^ fk

  + bridge table as an employee can belong to many departments
  + uses a composite key rather then defining a single primary key
---------------------------------
! EMPLOYEE_SID ! DEPARTMENT_SID |  
---------------------------------
  ^ cpk / fk     ^ cpk / fk

  + bridge table defining a single primary key
----------------------------------------------------------- 
| EMPLOYEE_DEPARTMENT_SID ! EMPLOYEE_SID | DEPARTMENT_SID |
-----------------------------------------------------------
  ^ pk                      ^ fk           ^ fk

  + a table with a foreign key to the bridge table
------------------------------------------------------
! SHIFT_SID | EMPLOYEE_DEPARTMENT_SID | HOURS_WORKED |
------------------------------------------------------
  ^ pk        ^ fk                 

pk = PRIMARY KEY, fk = FOREIGN KEY, cpk = COMPOSITE PRIMARY KEY

回复收藏 0 原文

~没有更多了~