如果我将数据库设计的这一部分的标识关系更改为非标识关系，这意味着什么？
我对这个数据库设计有疑问。我有点不确定数据库中识别和非识别关系之间的区别，这导致我脑子里有些困惑。
我有这样的数据库设计：（有点像电影租赁店。“朋友”是借电影的人。“工作室”是合作制作电影的制作工作室。）
我有点理解它是如何工作的。但是，我想知道如果我在贷款表中创建一个 loan_id ，并使用 movie_id 和 friend_id 作为普通外键会怎样？
我的一些问题是： 后一种方法的优点或缺点是什么？ 初始模型或后续模型更好的情况？ 最初的模型是否可以让朋友多次借阅电影？
任何彻底的解释将不胜感激。
I have a question regarding this database design. I am a bit unsure of the difference between identifying and non-identifying relationships in a database leading me to some puzzles in my head.
I have this database design: (kind of like a movie rental stores. "friend" are those who borrow the movie. "studio" is the production studios that collaborated in making the movie.)
I somewhat understand how it works. However, I was wondering what if I create a loan_id in the loan table, and use movie_id and friend_id as normal foreign keys?
Some of my questions are:
What are the advantages or disadvantages of the later approach?
A situation where the initial or later model is better?
Does the initial model enable a friend to borrow a movie more than once?
Any thorough explanation would be much appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
拥有所有多对多表（表协作、贷款、角色）的方式称为复合主键：其中两个（或更多）列形成唯一值。
当您有复合主键时，许多数据库设计者更喜欢创建代理主键（例如您建议的loan_id）。我是其中之一。这篇文章很好地阐述了为什么或为什么不的争论：复合主键与唯一对象 ID 字段。
我相对简单的原因是，复合键往往会增长：以贷款为例，如果电影多次贷款会发生什么？使用复合方法，您必须将loan_date 添加到复合键中。
如果您想跟踪某种类型的再贷款怎么办？然后，您必须有一个第二个表，其中包含贷款表中的所有复合 pk 字段（original_loan_movie_id、original_loan_friend_id、original_loan_date），以引用原始贷款...
The way you have all of your many-to-many tables (tables collaboration, loan, role), is called a composite primary key: Where two (or more) columns form a unique value.
When you have a composite pk, a lot of db designers prefer to create a surrogate primary key (like your proposed loan_id). I'm one of them. This post does a good job going through the arguments of why or why not: Composite primary keys versus unique object ID field.
My relatively simple reason for it, is composite keys tend to grow: Using the loan example, what happens if that movies loaned more than once? Using the composite approach, you would then have to add loan_date to the composite key.
What if you then wanted to track re-loans of some sort? You would then have to have a 2nd table carrying all the composite pk fields from the loan table (original_loan_movie_id, original_loan_friend_id, original_loan_date) just to refer to the original loan...
在
LOAN表中，您需要保证以下列是唯一的：copy_id）...因为我或其他任何人都应该能够多次租借同一部电影。这些也是最有可能搜索的列...
考虑到这一点，将名为
loan_id的列定义为表的主键的想法是多余的。 ORM 一直要求使用非复合主键来简化查询...但它使查询更容易...
乍一看，它使删除或更新特定贷款/等变得更容易 - 直到您意识到您需要知道适用的 id 值首先。如果您必须根据电影、用户/朋友和日期搜索该 id 值，那么您最好首先直接使用标准。
但复合键很复杂......
在这个例子中，主键约束将确保三列——movie_id、friend_id和loan_date——是唯一的并被索引（如果聚集索引不支持的话，现在大多数数据库会自动索引主键）尚不存在）使用表可能的最佳索引。
单独主键方法意味着
loan_id使用表的最佳索引进行索引（SQL Server 和 MySQL 称它们为聚集索引，对于 Oracle 来说它们都只是索引），并且需要额外的复合索引唯一约束/索引。某些数据库可能需要超出唯一约束的额外索引...因此，这使得数据模型更加复杂/复杂，并且没有任何好处：结论
我还没有看到单列主键优于复合主键的合理理由。
In the
LOANtable, you'd need to guarantee the following columns are unique:copy_idassuming there are multiple copies of a movie)...because I, or anyone else, should be able to rent the same movie more than once. These are also the columns most likely to be searched on...
With that in mind, the idea of defining a column called
loan_idas the primary key for the table to be redundant. ORMs have been mandating the use of non-composite primary keys to simplify queries...But it Makes Queries Easier...
At first glance, it makes deleting or updating a specific loan/etc easier - until you realize that you need to know the applicable id value first. If you have to search for that id value based on a movie, user/friend, and date then you'd have been better off using the criteria directly in the first place.
But Composite keys are Complex...
In this example, a primary key constraint will ensure that the three columns--movie_id, friend_id and loan_date--will be unique and indexed (most DBs these days automatically index primary keys if the clustered index doesn't already exist) using the best index possible for the table.
The lone primary key approach means the
loan_idis indexed with the best index for the table (SQL Server & MySQL call them clustered indexes, to Oracle they're all just indexes), and requires an additional composite unique constraint/index. Some databases might require additional indexing beyond the unique constraint... So this makes the data model more involved/complex, and for no benefit:Conclusion
I've yet to see a legitimate justification for a single column primary key over a composite primary key.