CODEWITHSUNDEEP
mysqldatabase
Sean
Sean

Reputation: 43

Is it ok to reference two foreign keys when the primary key is in a different table?

Author(AuthorID, AuthorName, Address, TelephoneNo, PublisherCode)

Book (BookID, Name, ReleaseDate, Price, AuthorID)

Publisher(PublisherID, Name, Address, AuthorID)

PK = Bold

FK = Italic

I am trying to write a query which

Will illustrate the books and their various Publishers and group by PublisherID

I get the general idea of retrieving this information but I would like to know if it is ok to references a FK to another FK.

For example book.AuthorID = publisher.AuthorID as the PK AuthorID is not in either of the tables in the query.

SELECT b.name. p.name
FROM Books b
INNER JOIN Publisher p ON b.authorID = p.publisherID
GROUP BY publisherID

Upvotes: 1

Views: 141

Answers (1)

HYAR7E
HYAR7E

Reputation: 304

I think with "FK to another FK" you mean getting PublisherCode (book(authorID) -> Author(PublisherCode) -> Publisher).

I wouldn't do it cuz Publisher is a property of the book, not of the Author, therefore you should store the PublisherID reference in you Book table

Book (BookID, Name, ReleaseDate, Price, AuthorID, PublisherID)

Also if you store PublisherID within Book table, it will allow only one publisher, since the query you are required for is to show "the various Publishers" of each book, you should use another table to represent that interaction

BookXPublisher (BookFK, PublisherFK)

This table only have two Foreign Keys, and will store data like this

(1 , 1) BookID:1 && PublisherID:1
(1 , 2) BookID:1 && PublisherID:2
(3 , 1) BookID:3 && PublisherID:1

You can add another table for BookXAuthor to the same purpose

With that in mind you query will be

SELECT b.name. p.name
FROM Books b
INNER JOIN BookXPublisher bxp ON b.BookID = bxp.BookFK
INNER JOIN Publisher p ON p.PublisherID = bxp.PublisherFK

And your tables like this:
Book (BookID, Name, ReleaseDate, Price)
Publisher(PublisherID, Name, Address)
BookXPublisher (BookFK, PublisherFK)

Additionally, with GROUP BY you can count how many books have published a author o a Publisher

Upvotes: 1

Related Questions