What would it mean If I change the identifying relationship from this part of a database design to a non-identifying relationship?

Question

Hi all, I have a question regarding with 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.)

View the images here (Sorry about the links.. (I don't have 10 reputation to post images .. x{ )

which 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.

Answer 1

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: http://stackoverflow.com/questions/159087/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...

Answer 2

In the LOAN table, you'd need to guarantee the following columns are unique:

• movie_id (replace with copy_id assuming there are multiple copies of a movie)
• friend_id
• loan_date

...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_id as 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_id is 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:

• Some databases, like MySQL, put a limit on the amount of space you can use for indexes.
• the primary key is getting the most ideal index yet the value has no relevance to the data in the table, so making use of the index related to the primary key will be seldom if ever.

Conclusion

I've yet to see a legitimate justification for a single column primary key over a composite primary key.