Should I index a foreign key that is going to be updated often

Question

I am trying to create a library relational database, in which there are two tables: users and books. The relationship is one to many:one. A user has many books, and one book is owned by only one user. I was thinking that the book table should have a foreign key column that references the user id.

However I encountered a problem if I want to get all of the books of a given user. The only option is to query the books whose user id equals the given user id using join. But if there are many books it will take a lot of time. So one may suggest to index the foreign key column as a non clustered index. However a book-user combination will be updated often--you don't keep a book more than one day in this library. But I read that update an indexed column often is not the best practice.

So what should I do? What is the best solution for this case?

Possible duplicate of [Should I make a foreign key that can be null or make a new table?](https://stackoverflow.com/questions/35333340/should-i-make-a-foreign-key-that-can-be-null-or-make-a-new-table) — philipxy, Oct 21 '17 at 10:35
It is not duplicated. My main issue is should I index column that is updated often, and if not what is the right solution — Raf Raf, Oct 21 '17 at 10:39
Your main issue is it's a bad design. Suggest you read the link & others like it. — philipxy, Oct 21 '17 at 10:57
Normally there is a table for books, one for users & one for user_has_borrowed_book. You *could* instead store the table for users & a table combining the other two, using NULL. Which to prefer for what reasons is a matter of quality of design. (Including implementation issues.) The first design is straightforward, the second is not. Find & follow a text on information modeling & database design. We forego a straightforward design when we can demonstrate it has performance problems & another does not. You need more experience with the former before you can appreciate the latter. — philipxy, Oct 21 '17 at 11:09

score 0 · Answer 1 · answered Oct 21 '17 at 11:02

0

Best performance for bi-directions query should include a middle table to storage the relationships. Both of customer and book should have unique index

The middle table - borrowing_table

with column user_id and book_id
You storage the information of both users and books index (id) on this table, so you can query the table by user_id and get which books have been borrowed by this individual, you also can get the users quick from the query by books_id.

answered Oct 21 '17 at 11:02

slee

239
1
4

First, it can be simplified: the relationship is one to many and not many to many-- a book can only be owned by one user. Your solution solved the performance of querying the list of books own by a user. But another query is to update the book owner and this is not solved correctly in your solution considering performance – Raf Raf Oct 21 '17 at 11:30
The book table don't require any update when the book change of the user or user return the book – slee Oct 21 '17 at 11:54
But if a user wants to borrow a book, I need to query whether the book is borrowed already or not – Raf Raf Oct 21 '17 at 12:04

score 0 · Answer 2 · answered Oct 21 '17 at 11:30

You should have an index on book_id.

Your concern about "frequent" updates just doesn't apply in a library setting. Libraries work on the time frames of days and weeks. Databases work on the timeframes of milliseconds, seconds, and minutes. What might seem frequent in a library is rather rare from the perspective of a database.

That said, I would suggest an intermediate table, not because you have a 1-n relationship at any given point in time. Instead, you have a time-tiled relationship. So:

create table UserBooks (
    UserBookId int,  -- serial, auto_increment, identity, generated always
    UserId int references Users(UserId),
    BookId int references Books(BookId),
    FromDate datetime,
    ToDate datetime,
    DueDate datetime,
    OverdueFees numeric(20, 4)
    . . .
);

In other words, "borrowing" deserves to be entity itself, because there is more information than just the book and the user.

I understand what you are saying about the frequency, but still updating the index every week is not going to require rebuild the index once in a short while (and as I read it is not a good thing) — Raf Raf, Oct 21 '17 at 11:47

Should I index a foreign key that is going to be updated often

2 Answers2