I am trying to build a database for a bookstore. The following is a smaller subset of the overall design.
Currently I have a Book entity with ISBN (primary key), title, and some others as attributes.
I have a Author entity with author_id (primary key), name.
Since book and author are a many-to-many relation. I have Authorted_title associative entity with ISBN and author_id as attributes. So ISBN is a Foreign key to Book entity and author_id is a Foreign key to Author entity.
My question is, is it possible to have ISBN from Book entity as Foreign key to and author_id from Author entity as Foreign to the respective attributes in the associative entity Authored_title than the other way around (as opposed to what I mentioned in previous paragraph)?
I am kind of confused after thinking about it. Is there even “to” and “from” kind of thing between tables when it comes to FK?
Advertisement
Answer
The idea of a foreign key constraint is that it prevents creating a record in a table X if there is no record in table Y that X relates to. In your case, the Author and Book have the primary keys and the Book_Author table that breaks the M:M relationship down to two 1:M relationships is keyed to Author and Book; you cannot create a record in Book_Author until you have first created a record in Book and also a record in Author
You appear to be asking if you can do this the other way around, and key the Book table and the Author table to the Book_Author table
The answer is No (well, nothing is a no, but this is a really-really-shouldn’t)
In order to accept foreign key relationships, Book_Author has to have a primary key. What will you choose for the primary key? You can use BookID+AuthorID. You could have a separate incrementing int, or a guid, but putting that kind of PK on a table that breaks down a M:M association is up on the list of “just because you can, doesn’t mean you should”. You cannot establish a foreign key to just part of a primary key, so if Book and Author are to be keyed to Book_Author, and Book_Author is to have a composite PK then the other tables (Book, Author) will need additional columns to cover the other elements of the key; you’ll end up storing an author in the book table and a book in the author table. What then for a book with two authors? It’ll need two entries in the Book table, one for each author.. We’re supposed to be actively removing this soft of data duplication as we normalize our database. This step is actively de-normalizing. The same argument exists for putting an incrementing PK on your Book_Author table; how do you represent that a book has 2 authors? You need 2 rows in your association table, and they can’t have the same PK value, so let’s give them different values:
BookAuthorId, Book, Author 1, GoodOmens, NeilGaiman 2, GoodOmens, TerryPratchett
Now we have to create Books for these, Books that reference our primary key column in Book_Author:
Book Name, BookAuthorId, Description Good Omens, 1, A funny story about life as a fallen angel Good Omens, 2, A funny story about life as a fallen angel
The more time you spend thinking about this concept that “Book_Author should be the primary key, and the Book and Author tables should foreign key to it” the more you’ll realize that it’s absolutely unworkable and solves nothing, it just creates headaches
One Book, one row in Book table. One Author, one row in Authors. We have these rules because there is only one Terry Pratchett, one Neil Gaiman, one story called Good Omens. All these “one” things should have one row, a primary key identifies it.. and when we want to link these things together in combination, we use another table that has a composite key that ensures we can’t record Neil Gaiman twice as having written Good Omens, and the foreign key to ensure that the Book and the Author we’re linking together really does exist.. No point assigning books to nonexistent authors, and nonexistent books to authors..
So, this is why these things are “that way” round; the “other way round” has no benefit