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

Question

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?

score 5 · Accepted Answer · answered Feb 10 '16 at 11:35

5

Answer: None.

Reason: This is a many-to-many relationship. An author can have any number of books written, also a book can have more than one author.

Solution: Make a separate table with two columns author_id, book_id both are foreign keys.

answered Feb 10 '16 at 11:35

Majeed Siddiqui

548
2
9

This makes a very valid sense if OP is allowed to create a new table +1 – Rahul Tripathi Feb 10 '16 at 11:36

score 2 · Answer 2 · answered Feb 10 '16 at 11:53

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.

score 1 · Answer 3 · answered Feb 10 '16 at 11:33

1

You can make Author as your parent table and Books as child table.

Reason: An author can write multiple books but a book(usually) is written by one author only.

answered Feb 10 '16 at 11:33

Rahul Tripathi

168,305
31
280
331

I agree with you. But multiple authors can also contribute to one book too. @Rahul Tripathi – 1000111 Feb 10 '16 at 11:34
2

@SubrataDeyPappu:- Yes thats why I added `usually`. Also the chances of multiple authors contributing to one book would be very rare/less – Rahul Tripathi Feb 10 '16 at 11:35
That's a valid solution. (You could even add abstract authors, such as writing duos, e.g. 'Kami Garcia & Margaret Stohl', to the authors table, so as to cover multiple authors for one book. That would be perfectly fine as long as it doesn't matter to you that there is no relation between the single author and their collaborations in the database then.) – Thorsten Kettner Feb 10 '16 at 12:14

score 1 · Answer 4 · answered Feb 10 '16 at 11:37

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).

score 0 · Answer 5 · answered Feb 10 '16 at 11:40

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.

score 0 · Answer 6 · edited May 23 '17 at 12:15

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.)

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

6 Answers6

Linked