Re: Database Design Problem...

Jimmy,

You are absolutely right. I guess your friend did single table look-up, order table, most likely. For users, when display orders, you should join your book table with order table via book_id column (I guess you have indexes for the book_id columns in both tables). You can also create RI with book_id of the book table as the parent and that of the order table as the child, so that the order can be created only when the book is in the book table. This will reduce typo errors.

Now back to the book_id. Why should you use it instead of the title? book_id's data type is number. Therefore, it has fewer combination than characters (256 combination). Therefore, it is more efficient internally when you do logical compare (such as where book_id = 445). Whereas book_title is long, it takes longer time and especially as a primary key it would be a bad choice. Your index will be much larger, which means more i/o. When joining two tables, no matter which database, it is more efficient using number.

For more database design, you can read my data modeling teaching material at http://www.doc.anl.gov/dmclass/ Received on Mon Mar 09 1998 - 00:00:00 CST