Re: Data Modeling Problem

> > In your experience, how much difference does it make in terms > >of performance, if the data types of fields used in joins are characters, > >as opposed to numbers? If we added serial/integer fields to be the > >primary/foreign keys respectively in this application, would that be worth > >a 4 day effort to implement the change? > > All in all, what are the pros and cons in selecting a piece of > >data (character or otherwise) as the primary/foreign key, as opposed to a > >system-assigned number? > > In my experience, a SERIAL type field is much faster than any character field. > Also with the SERIAL field, you can change the tag and not have to dela with > propagating changes. I would say that the 4 day effort is more than worth it. > Also by creating an ID field,you can define your tag to be a VARCHAR and save > some disk space. When you have to compare only 32 bits as opposed to 26*8 > bits, you know it's faster. The only "caution" I could add with this is, if your application is meant to be database-independent, SERIALS could cause a little problem down the road. The actual "SERIAL" datatype is not ODBC-standard; while most of the major databases maintain the concept of a SERIAL value (i.e. Oracle has a "SEQUENCE") not all of them do, and the since they may call them different things, or implement them slightly differently, doing a straight translation could create headaches later. You can, however, implement it programmatically by creating a "key server" table that maintains the next "key" value for each table. Your "key" value is just an integer. When you need a key for a table insert, you simply need to ensure that when you grab that value, the first thing you do is increment it (remember to LOCK before you do this!) before you actually go to use it in your table insert. - Corinne -- Corinne Gregory, Senior Consultant ASIX, Inc. 777 108th Ave. NE, Suite 1830, Bellevue WA 98004-5118 corinne.gregory@asix.com