Re: Database Design Problem...

In article <3504A096.8CB@comp.polyu.edu.hk>, Jimmy <c6635500@comp.polyu.edu.hk> 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. > >Thanks, >Jimmy 1. Why would you assume book_name is going to be unique? How many books titled, say, "George Washington" do you think there are? How would you identify 2nd, 3rd or 4th editions of the same book? 2. Even if it was unique, you don't want to use a large character string as the primary key on a table, particularly when many rows are going to contain the same characters in the first nnn positions (how many titles do you suppose start with the word United, or History, etc?). 3. Suppose you want multiple copies of the same book? Just a few thoughts... Ray