MySQL - Relational tables - How to create multiple relationships?

Question

I have two tables in my database.

Table 1 : book
book_id (Primary & Auto Increment)
book_name
writer_id (Foreign Key from writer table, selected as index)

Table 2 : writer
writer_id (Primary & Auto Increment)
writer_name

I can add only one writer to a book, but a book can be written by two or more writers. How can I achieve that?

I thought I could create two tables called writer_2 and writer_3 (since most books are written by 1, 2 or 3 writers) and add them as a foreign key to my table, but I am open to alternative solutions.

I am using phpmyadmin and my table storage engine is InnoDB.

score 1 · Accepted Answer · answered Dec 14 '14 at 16:04

1

You need a junction table that assigns writers to books. This would be something like:

create table BookWriter (
    Book_Id int not null,
    Writer_Id int not null,
    foreign key (Book_id) references book(book_id),
    foreign key (Writer_Id) references writer(writer_id)
);

Note that you could have additional information in this table, such as an alias:

create table BookWriter (
    Book_Id int not null,
    Writer_Id int not null,
    Alias varchar(255),
    foreign key (Book_id) references book(book_id),
    foreign key (Writer_Id) references writer(writer_id)
);

For instance, "Kilgore Trout" who wrote a book called "Venus on the Half-Shell" was really Kurt Vonnegut.

answered Dec 14 '14 at 16:04

Gordon Linoff

1,242,037
58
646
786

Now I got it, that's way I can assign more than one writers to a book. – salep Dec 14 '14 at 16:12
@salep . . . Yes, you would just have multiple rows in the table, one for each write on the book. – Gordon Linoff Dec 14 '14 at 16:15
Just one more question, would it be wise to create a table called "book_total" rather than BookWriter and assign all foreign keys from other tables (book_id, writer_id, book_image_id, book_categories_id, etc) related to a book in this particular table? I will be using the same structure you proposed. Then I could easily do a SQL query and get all the results I want related to a particular book. – salep Dec 14 '14 at 16:35
@salep . . . No. You should have a separate junction table for each n-m relationship, such as categories and images. Putting them into the same table causes problems with enforcing foreign key relationships and complicates the queries reading the values. – Gordon Linoff Dec 14 '14 at 16:38
Okay, but I will have to create lots of tables in my database in my case. Wouldn't it be a problem for development purposes when I want to scale the database? So I should create a table that contains two columns for each relation, right? – salep Dec 14 '14 at 16:40
1

@salep . . . For each relation that has multiple assignments. Obviously, a book only has a single title (as an example) so you don't need a junction table for that. – Gordon Linoff Dec 14 '14 at 16:57

score 1 · Answer 2 · edited May 23 '17 at 11:50

1

What you are looking for is a many-to-many relationship.

Table 3 : bookwriter
writer_id
book_id

Here you can get more details, also google.

edited May 23 '17 at 11:50

Community

1
1

answered Dec 14 '14 at 16:06

Adrian Nasui

1,054
9
10

MySQL - Relational tables - How to create multiple relationships?

2 Answers2