Re: Database Design Problem...

From: Jean-Marc van Leerdam <Jean-Marc.van.Leerdam_at_xxremovexx.ingbank.com>
Date: 1998/03/12
Message-ID: <3507e3fe.26807977@news.ing.nl>#1/1

Jimmy <c6635500_at_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) --+ Received on Thu Mar 12 1998 - 00:00:00 CST