DataBase design: one to many database?

Question

I am very new to database designing.

I have a doubt. The question is very basic. But please help me out. i will try to expain it through an example.

Suppose, I got books in one table and their authors in the other(assuming that one book is written by just one author(one to many) and one author can write many books(many to one)). I am not getting how exactly to link the tables and what should be auto-incremented?

tblBooks     //Table 1 contains two entities
    {
     bookId      // This field is auto-incremented
     bookName
    }


tblAuthors    //Table 2
    {
     authorId    // Should this field also be auto-incremented?
     authorName
     Field3      // What should be the 'Field3' then which acts as link between both the tables?
                 // What exactly is foreign key? Here 'Field3' would be foreign key or what?

    }

Help appreciated

authorID would be autoincremented, and Field3 would be actually in tblBooks and be called authorID to link them — Patrick Evans, Jun 04 '13 at 17:08
@PatrickEvans so in one-to-many relation, the linking field resides in the first table? is this fiels known as foreign key? — Navneet Saini, Jun 04 '13 at 17:11
Yes, the "Many" table gets a foreign key of the "One" table. — Patrick Evans, Jun 04 '13 at 17:21

Patrick Evans · Accepted Answer · 2013-06-04T17:37:38.970

The "Many" table gets a foreign key of the "One" table.

tblBooks {
    bookId   
    bookName
    authorId
}
tblAuthors {
    authorId  
    authorName
}

Example queries

//Grabs EVERY book that was made by an author with id 1
SELECT * FROM tblBooks where authorId='1' 

//Grabs the author of the book with id 223
SELECT * FROM tblAuthors where authorId=(SELECT authorId FROM tblBooks WHERE bookId='223')

//Joins the tables so each book will show its author
SELECT 
    tblBooks.bookId,
    tblBooks.bookName,
    tblAuthors.authorName
    tblAuthors.authorId 
FROM 
    tblBooks 
JOIN 
    tblAuthors 
ON 
    tblBooks.authorId=tblAuthors.authorId

The syntax will probably change depending on what database you use (mysql,oracle,sqlite etc) but thats the basic structure.

And if you decide to go with a many-to-many structure you can do a couple things, one create a third table that works to link both tables e.g. Books that have many authors:

tblBooks {
    bookId
    bookName
}

tblAuthors {
    authorId
    authorName
}

tblBookAuthors {
    bookId
    authorId
}

OR have a field in one of the tables that has a comma seperated string of author ids:

tblBooks {
    bookId
    bookName
    authorIds
}

tblAuthor {
    authorId
    authorName
}

authorIds would be like 1,12,32, in this case you would have to use a database function to select the authors IN that set, for example MYSQL has find_in_set(tblAuthors.authorId,tblBooks.authorIds) where the first argument is the search and the second is the set of data you are searching

And the way to decide which table in the many-to-many structure gets the field with the comma separated ids is the table where the foreign ids dont get added removed often, for example the authors dont usually get removed or added to a book so it gets the list field.

Could you please help me with this question too? http://stackoverflow.com/questions/16924499/database-image-file-as-blob-or-file-path?noredirect=1#comment24431826_16924499 — Navneet Saini, Jun 04 '13 at 18:05

score 3 · Answer 2 · answered Jun 04 '13 at 17:12

3

@Patrick evans is correct. Linking field is in child table. To lay it out for you,

tblBooks     //Table 1 contains Books
{
 bookId     Primary Key // This field is auto-incremented
 bookName
 AuthorId   Foreign Key constraint references tblAuthors.AuthorId
}


tblAuthors    //Table 2
{
 authorId,  Primary Key // this field can also be auto-incremented
 authorName
}

answered Jun 04 '13 at 17:12

Charles Bretana

143,358
22
150
216

Okay, I got that! So, can i conclude that in one-to-many relationship the foreign key always lies in the first table(the table which relates one to many)? – Navneet Saini Jun 04 '13 at 17:14
If by that you mean the child table, (on the many side of the 1<=>many relationship), then yes. – Charles Bretana Jun 04 '13 at 18:11

score 1 · Answer 3 · answered Jun 04 '13 at 17:14

1

Field3 could be placed in tblBooks and called, for instance, authorId defined as a foreign key. You can add a constraint like this

ALTER TABLE tblBooks
ADD CONSTRAINT fk_author
FOREIGN KEY (authorId)
REFERENCES tblAuthors(authorId)

In this case bookId should not be unique, because there will be several inputs in table tblBooks with the same authorId.

By the way, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.

answered Jun 04 '13 at 17:14

Misa Lazovic

2,805
10
32
38

Could you please explain. First Thing, should I set bookId and authorId both to auto-increment? – Navneet Saini Jun 04 '13 at 17:18
You could, but it's not necessary. – Misa Lazovic Jun 04 '13 at 17:20

DataBase design: one to many database?

3 Answers3

Linked