Re: Database Design Problem...

I agree with the other posts that say to keep the design as you have it with an exception. Instead of generating the ID as MAX+1, I would implement a sequence to generate the ID. If two people are entering a new book at the same time, it is possible to have duplicate ID's.

Michael.

Bret Halford wrote:
>
> Jimmy wrote:
> >
> > Hello all,
> >
> > I have a databases design problem.
> > Recently, I want to implement a table which stores information about
> > books.
> > So I decide to create the table BOOK with the following attributes:
> >
> > book_id number,
> > book_name char(10),
> > author char(30)...
> >
> > book_id is a primary key and is automatically generated by the system.
> > (max + 1)
> > Then another table, ORDER, which is used to store the inforamtion about
> > the order. One of the attributes of this table is book_id, which is a
> > foreign key and related with the book_id of the table BOOK.
> > I think the design is OK.
> > However, one day, a person ask me, "Why you put book_id in the table
> > BOOK? book_name is unique all the time, why you create additional column
> > (book_id) in table BOOK? Moreover, I don't know which book is from the
> > table ORDER since it contains book_id only. book_id is meaningless. Why
> > don't you delete the book_id column in table BOOK and replace book_id
> > with book_name in table ORDER?"
> >
> > I think create the ID column for the book (or other table) is common in
> > the relational database. Although the book name is unique, create an ID
> > column is much easier to manipulate. Am I right? Or should I delete the
> > ID column? Any comment will be appreicated.
> >
>
>
> Your friend is mistaken, book titles are >NOT< unique. While publishers
> usually try to avoid duplicate titles, they can and do happen. An
> example: Both Jackie Koller and John Tanner have authored books titled
> "The Falcon". Furthermore, a book_id can be used to differentiate
> different editions (version, paperback vs hardcover, book-club edition,
> etc) of the same book.
>
> Your schema also seems to be assuming that each book has a single
> author, which just isn't so in real life. The book "Physical Database
> Design for Sybase SQL Server", for instance, has three: Rob Gillette,
> Dean Muench, and Jean Tabaka.
>
> You might want to have a look at the "pubs2" sample database that
> comes with SQL Server. While it is not the ultimate design for a
> publisher's/bookseller's database, it is a pretty good start.
>
> --
> Bret Halford Imagine my disappointment
> Sybase Technical Support in learning the true nature
> 3665 Discovery Drive of rec.humor.oracle...
> Boulder, CO 80303
Received on Mon Mar 09 1998 - 00:00:00 CST