4

I am creating a database containing books that I own and have read. I want to track both the book (or "title") that I own and read, and the edition (or "physical bound paper") of that book that I own and read.

Book and Edition are many-to-many. I own multiple editions of the book Democracy in America. I also own an edition called "Hemingway" that contains several books (or "titles"), including one called "For Whom the Bell Tolls".

Thus, I need a bridge between book and edition. My tables are:

Book (book_pk*,title)
Edition (edition_pk*,ISBN,year)
Book_Edition (book_fk,edition_fk)

I believe it is correct to say that the Book_Edition table contains a composite primary key.

Now, I am working on my Read table, which will contain the books that I have read and the date on which I read them. My read table so far contains:

Read (read_pk,date,note)

However, I now need to tie my Read table to my books and editions. It appears to me that book_fk and edition_fk are transitively dependent in this case. So how do I comply with the third normal form?

Option 1:
Modify the Read table to: Read (read_pk,date,note,book_fk,edition_fk)

Option 2:
Modify the Book_Edition table to: Book_Edition (book_edition_pk,book_fk,edition_fk)
Modify the Read table to: Read (read_pk,date,note,book_edition_fk)

Option 3: ???

Any insight would be appreciated. Apologies if this has been treated elsewhere; I saw a couple posts that looked promising but as a relative n00b I was not able to decipher them and apply them to my situation.

EDIT per sqlvogel:
Let me take a stab at identifying dependencies -- that is, I am trying to identify places where if Field A is changed, then Field B must or may change. I think I am finding this difficult because books (both "titles" and "collections of bound paper") are inherently permanent. The only time I would expect to edit the title, ISBN, or year fields would be if there is a data entry error. If the ISBN for a particular edition_pk is entered incorrectly, it's probably slightly more likely that the year for the same edition_pk was also entered incorrectly, but is that a dependency?

With respect to the read table, I believe the situation is similar. Records would be created each time a book is read, and theoretically never edited. I want to identify the book and edition that were read on a particular date. If there is a data entry error, it might affect one or more of the fields. In particular, if the wrong book_fk is entered, it's probably more likely that the wrong edition_fk was entered too. Again, is that a dependency I should be worried about?

Is there anything else I need to consider when thinking about dependencies?

NaMarPi
  • 164
  • 11
Nathaniel is protesting
  • 151
  • 7
  • Each option has pros and cons (what a surprise). If you go with option 2, you will want another constraint to ensure the combination of book_fk and edition_fk is unique. Personally, I like Option 1. – Dan Bracuk Sep 24 '13 at 22:48
  • I like option 1 as well. The redundancy is minimal and you're not losing capability. – Hart CO Sep 24 '13 at 22:57
  • 2
    @Nathaniel, to answer your question properly, you really need to list out the dependencies you expect these tables to satisfy. Any answers based only on a list of column names are going to be mainly guesswork. – nvogel Sep 25 '13 at 18:50
  • 1
    Re your edit: You misunderstand what a functional dependency is. – philipxy Jan 07 '15 at 08:04

2 Answers2

4

Option 1: Read (read_pk,date,note,book_fk,edition_fk)

Assumptions:

{read_pk}->{date,note,book_fk,edition_fk}
{read_pk} is the primary key of Read.

For the sake of example just suppose that {book_fk,edition_fk}->{date}, meaning that each book is read only once (only a single date per book/edition). If you didn't make {book_fk,edition_fk} a candidate key in Read then {book_fk,edition_fk}->{date} would be an example of a non-key dependency in violation of 3NF because the determinant is not a key. The same would be true even if you substituted {book_edition_fk} in place of {book_fk,edition_fk}. i.e. your Option 2 is apparently the same as Option 1 as far as 3NF is concerned.

Since you haven't specified any dependencies I have just given this as an example. I can't say whether those dependencies would be a correct description of your situation. You yourself need to determine what dependencies actually should be in force here.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • I've updated my question with my thoughts on dependencies. I would allow for multiple reads per book/edition combination, so that would not seem to be a dependency. Your point about Options 1 and 2 being the same w.r.t. 3NF now seems obvious; thanks. – Nathaniel is protesting Sep 26 '13 at 05:47
-1

Transitive dependencies require the dependent attribute to be a non-key attribute. Since the two attributes you're concerned about are foreign keys, you do not have a transitive dependency problem in your structure.

You do not need to alter the original design.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • 3
    Normalization makes no distinction between attributes which are foreign keys and those which are not. The term "non-key attribute" means not part of any *candidate key* of the table in question (Read). That may or may not apply to some of the attributes here because the candidate keys and dependencies are not specified. The first two sentences of this answer are incorrect and it isn't possible to determine the correctness of the third sentence. – nvogel Sep 25 '13 at 18:48