Table design question

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? Thanks.

Don, Uhm, I'd use a serial field for a unique index, since this table is most likely going to be cross referenced in other tables. Thus a unique identifier. Now as a side note, I do believe that Informix won't use any index for a table with less than 2000 rows? (My memory is fuzzy, is it 2K rows or 200 rows that the engine does a sequential scan?) Also if you do have 2K employees/staff , then you'd also have other unique identifies like department number, building number, location, etc ... while these don't give you unique rows back, if you were to design a popup list for a form, you'd want to build the list dynamically from selecting only those employees in that location/dept/building/etc .... For a simple reasoning, look at it this way. Your serial is only 4 bytes long. Since this table isn't isolated, by this I mean there will be other tables using this data, you will then have a problem. You would either duplicate this data (60bytes per row per each table) versus duplicating the identfying field which is 4 bytes (integer). So you end up with a fat non-normalized system. Also, if this table were to be a stand alone table with no relationships to other tables, you would still want to have some method of finding a unique row quickly. I realized that others have answered the same way, but I just wanted to address your concern about trying to save 4 bytes per row. (Isn't this the same type of thinking which got us into the Y2K issue in the first place? Saving 2 bytes on the Year in a date field? :-) -Mikey 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? > > Thanks.

>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 > Use the serial field. Yes it adds a column, but when one of the staff has a name change you won't be trying to cascade the change throughout the rest of the system. Multi-column primary keys will also create more and more 'slow spots' as your code (ers) bludgeon the engine to death with complex joins.