Reputation: 403

Which table should be Parent table and which should be child table?

Hi have two tables Auther and Books. I am confused that to which table should i keep as parent table and which should be child table for making foreign key constraint.

CREATE TABLE author
 (
    author_id NUMBER(3) CONSTRAINT athr_aid_pk PRIMARY KEY,
    author_name VARCHAR2(30)
 );

 CREATE TABLE books
 (
    book_id NUMBER(3),
    book_title VARCHAR2(30),
    book_price NUMBER(3),

 );

Please explain me which table should be Parent table and why?

Upvotes: 2

Answers (6)

philipxy

Reputation: 15118

A FOREIGN KEY declaration says that each subrow of values that appears under listed columns of the table that REFERENCES also appears under the listed columns of the referenced table. The referenced columns have to be UNIQUE NOT NULL or a PRIMARY KEY. The "parent" table is the referenced one.

Whenever that's the case, declare a FOREIGN KEY. Otherwise don't.

It happens that if you JOIN on the two column lists, the result will have exactly one row per referencing table row.

With your two tables there's no foreign key to declare.

If you add a table author_books holding rows where "author AUTHOR_ID authored book BOOK_ID" then its author_id values have to be in author and its book_id values have to be in books. So declare two FOREIGN KEYs:

FOREIGN KEY (author_id) REFERENCES author (author_id)
FOREIGN KEY (book_id) REFERENCES books (book_id)

(If an author always wrote exactly one book then you could add book_id to author with a foreign key to books. If a book always had exactly one author then you could add author_id to books with a foreign key from author. But that's not the real situation with authors and books.)

Constraint declarations allow the DBMS to disallow erroneous changes that would make the database have a value that cannot be right. You don't need constraints to query a database. (Including UNIQUE NOT NULL, PRIMARY KEY or FOREIGN KEY.)

Upvotes: 0

Thorsten Kettner

Reputation: 94894

You have been given the correct answer already. I'd like to add the technical view: columns to add.

A foreign key constraint means you have a column or a set of columns in a table that uniquely identify a record in another table.

In order to have a foreign key on a book in table author you'd add book_id to that table. But that wouldn't make sense. Which of the author's books would you reference in this field? So books simply cannot be the parent table for the relation.

Then you can have it vice versa: Have author_id in table books, which would mean a book can only be written by one author. Does this suffice for what your database is to contain? Then this is the way to go.

These mentioned relations are 1:n relations (one author can write several books or one book can be written by several authors). But as mentioned, it is more typical for an author/book realation to be n:m (one author can write several books and one book can be written by several authors).

To introduce an n:m relation in a database you add a bridge table containing the IDs of both tables. And this bridge table would reference both parent tables with foreign keys.

Upvotes: 2

Md. Mainul Hasan Patwary

Reputation: 35

Explanation 1

If you think about one to many relation from author to book then author will be parent. Actually author will be the owner of the relationship. He will map the realtion from author to books table.

Explanation 2:

If you think about many to many relation from author to book then none of will be parent. Both of them will have equal importance.

Upvotes: 0

LikeAJohny

Reputation: 162

I'd go for another approach than having a parent and a child table. Create an extra table like this:

CREATE TABLE authors_books
 (
    author_id NUMBER(3),
    book_id NUMBER(3),
    PRIMARY KEY (`author_id`, `book_id`),
    CONSTRAINT `FK_author_books__author`
        FOREIGN KEY (`author_id`)
        REFERENCES `author` (`author_id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `FK_author_books__book`
        FOREIGN KEY (`book_id`)
        REFERENCES `books` (`book_id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
 );

This way you'll avoid any trouble with your n to m relationship between authors and books. Now one book can be written by multiple authors as well.

You probably wanna have an extra field orderas well to always maintain a unique PK. So the PK would be PRIMARY KEY (author_id,book_id,order).

Upvotes: 1