Re: Database Design Problem...

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