Re: Data Modeling Problem

In article <3pqka3$qgp@tribune.usask.ca> Viswanath_Aiyah@engr.usask.ca (Viswanath Aiyah) writes: >From: Viswanath_Aiyah@engr.usask.ca (Viswanath Aiyah) >Subject: Data Modeling Problem >Date: 22 May 1995 18:14:27 GMT >Hello: > > Presently, the primary key of the root device table (and >the primary/foreign keys of all the child device-type-specific tables) is >the char(26) tag. The primary key of the document table is the document >title, again a char(20 something) field. > > 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.