inserting multiple values in single column

[deolalkar_pooja]

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

[Celt.Max]

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? :)

[debasisdas]

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)

[debasisdas]

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)