Re: Table design question

We met the same problem in our application system. Our problem was the tables and programs are there for years. Most of the tables didn't have primary key or even unique indices. Then the DBA wanted to NORMALIZE the table and added a serial column to each of these tables. But what's the point there if the application doesn't reference the serial columns? Yes a serial surrogate key is much better than a composite key in terms of storage and performance ONLY if the application programs are designed with the thought of them and utilize them. Correct me if I'm wrong. Dong >From: "Mark D. Stock" <mdstock@informix.com> >Reply-To: mdstock@informix.com >To: Don Pritchard <please_reply@newsgroup.com> >CC: informix-list@iiug.org >Subject: Re: Table design question >Date: Fri, 19 Mar 1999 15:58:07 +0200 > >Don Pritchard wrote: >> >> Hi All >> >> I've always battled against this one : >> >> If you were creating a staff table >> >> Would you have the following >> >> staff_id serial >> fname char(20) >> mname char(20) >> lname char(20) >> .. >> .. >> .. >> >> where obviously staff_id is the primary key >> >> *or* >> >> fname char(20) >> mname char(20) >> lname char(20) >> .. >> .. >> .. >> >> Where the primary key is a combo of the first 3 fields >> >> The second is a bigger index but it dispenses with a code field. >> >> Any thoughts? > >I would definitely go with a surrogate key. It keeps the key (index) >size down (4 bytes instead of 60) and improves performance. Which is >exactly what the SERIAL type was designed for. > >Cheers, >-- >Mark. > >+----------------------------------------------------------+-----------+ >|Mark D. Stock - Informix SA http://www.informix.com |//////// /| >|mailto:mdstock@informix.com http://www.informix.com/idn |///// / //| >|http://www.iiug.org +-----------------------------------+//// / ///| >| Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| >| Fax: +27 838250 2325 |If it's fast, the users keep quiet.|// / /////| >|Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| >+----------------------+-----------------------------------+-----------+ > Get Your Private, Free Email at http://www.hotmail.com