0

hi to all, I created following table

create table book(id integer, author varchar(20))

In this, each book may have 1 or more authors.

My question is that, how can insert more value to author column like as follows.


id author
1 pooja merry james
2 robert stephen

Thanks & regerds,
Pooja

3
Contributors
3
Replies
6
Views
6 Years
Discussion Span
Last Post by debasisdas
0

Well, there are more ways on my mind how to do this...

1) make it up with just one row
- fill the Author collumn with names separated so it cant be easily machined with sql, but it should be no problem with other programming, eg.:

Id Author
1  E.M.Remarque | E.Hamingway

2) change the "book" table structure to following:

create table book(id integer, bookid integer, author varchar(20),  name varchar(20))
Id BookId Author            Name
1  1      E.M.Remarque      The Great Book
2  1      E.Hamingway       The Great Book

when you will be about to SELECT a book, just use some GROUP in your query

3) Use two tables with common ID

create table book(bookid integer, name varchar(20))
create table author(Id integer, bookid integer, Author varchar(20))
table book:
BookId   Name
1        The Great Book
2        The Greatest Book

table author:
Id BookId Author
1  1      E.Hamingway
2  1      E.M.Remarque
3  2      E.Hamingway

4) Use even three tables where one is so called cross table, this is the way I prefer personally

create table book(bookid integer, name varchar(20))
create table author(AuthorId integer, Author varchar(20), AboutAuthor varchar(2000))
create table book_x_author (Id integer, authorId integer, BookId integer)
table Book:
BookId   Name
1        The Great Book
2        The Greatest Book

table author:
AuthorId Author         About
1        E.Hamingway    Our king
2        E.M.Remarque   Also our king

table book_x_author:
Id   BookId   AuthorId
1    1        1
2    1        2
3    2        1

So? :)

0

what about this

1. book (book_id,book)name,author_id,....)
2. author (author_id,author_name,.......)

Composite key on book (book_id, author_id)

0

what about this

1. book (book_id,book)name,author_id,....)
2. author (author_id,author_name,.......)

Composite key on book (book_id, author_id)

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.