Member Avatar for 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

  • 3 Contributors
  • 3 Replies
  • 2K Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

You can do it, but you should not store multiple author names in one column. That breaks 1NF and makes searching, indexing, and updates painful. The cleaner fix is the many-to-many design hinted at in option 4 and the composite-key idea from . It also lets you preserve author order on the cover.

Here is a compact, production-friendly layout. Note the separate junction table and an optional AuthorOrder column to keep co-author order stable.

CREATE TABLE dbo.Books
(
  BookID   int IDENTITY(1,1) PRIMARY KEY,
  Title    nvarchar(200) NOT NULL
);

CREATE TABLE dbo.Authors
(
  AuthorID   int IDENTITY(1,1) PRIMARY KEY,
  AuthorName nvarchar(150) NOT NULL
);

CREATE TABLE dbo.BookAuthors
(
  BookID      int NOT NULL REFERENCES dbo.Books(BookID),
  AuthorID    int NOT NULL REFERENCES dbo.Authors(AuthorID),
  AuthorOrder tinyint NULL,
  CONSTRAINT PK_BookAuthors PRIMARY KEY (BookID, AuthorID)
);

To display authors in a single column for a book list, concatenate at query time. If your SQL Server supports STRING_AGG:

SELECT b.Title,
       STRING_AGG(a.AuthorName, ', ') WITHIN GROUP (ORDER BY ba.AuthorOrder, a.AuthorName) AS Authors
FROM dbo.Books b
JOIN dbo.BookAuthors ba ON ba.BookID = b.BookID
JOIN dbo.Authors a ON a.AuthorID = ba.AuthorID
GROUP BY b.BookID, b.Title;

On older versions, use the classic STUFF + FOR XML PATH approach:

SELECT b.Title,
  STUFF((
    SELECT ', ' + a2.AuthorName
    FROM dbo.BookAuthors ba2
    JOIN dbo.Authors a2 ON a2.AuthorID = ba2.AuthorID
    WHERE ba2.BookID = b.BookID
    ORDER BY ba2.AuthorOrder, a2.AuthorName
    FOR XML PATH(''), TYPE
  ).value('.', 'nvarchar(max)'), 1, 2, '') AS Authors
FROM dbo.Books b;

Tips: avoid varchar(20) for names; use nvarchar and a realistic length. Add an index on BookAuthors(AuthorID) if you will search by author. Enforce one row per (BookID, AuthorID) with the PK so duplicates cannot slip in.

Recommended Answers

All 3 Replies

Member Avatar for 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? :)

Member Avatar for 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)

Member Avatar for 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)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.