Re: Database Design Problem...

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. Always go for a key generation scheme that doesn't rely on the outside world for uniqueness. Book titles certainly are NOT unique. How many books are there titled 'The Bible' ? Or 'English Grammar' ... Also: using long character columns as key columns creates lots of duplications, wasting space in a database. HTH Jean-Marc. +------------------------------------------------------------+ |Jean-Marc.van.Leerdam@| All opinions expressed are just ... | |ingbank.com | opinions (and my personal ones!). | +-- (AntiSpam:note the xxremovexx in the reply-to address) --+