Re: Indexing by number or by varchar2

"Chris Hamilton" <toneczar_at_erols.com> wrote:

>Inaki Agirre <inaki.agirre_at_si.unirioja.es> wrote ...
>
>> If I must implement some codification, does it pay to define the primary
>> column as number or could I use a meaningful varchar2, say, (3) length.
>
>Better to use number datatype. The sort order is always correct (with
>varchar2 you must pad with zeroes for sorting to occur properly), and you
>save a little bit of storage. And, it's easily populated with a sequence
>called by a before-insert trigger.
>

But, on the other hand, it can make your programming harder to read and your application less efficient. Conventional database theory says that the codes themselves should be inherently meaningless. but it does slow things down.

For example, consider an extreme example where we even encode the SEX column of a Personnel record with a number instead of the obvious M or F ( or your language's equivalent ). ( I think Oracle Financials does this! ).

Then displaying the gender of everyone in the Personnel table becomes much less efficient:

     select p.name, g.gender_desc
       from gender_table g,
            personnel p
      where p.sex = g.gender_code

instead of

      select p.name, p.gender
        from personnel p.

Also, which is easier to read/maintain?

      select * from personnel p
         where p.sex = ( select g.gender_code
                           from gender_table g
                          where g.gender_desc = 'M' )

or

      select * from personnel
       where p.gender = 'M'

Extreme examples maybe, but the principle is common. When your real queries already join half a dozen or more tables, do you really want additional lookups which could have been avoided?

HTH, Dave.

-- 

To reply by email, remove the "no-spam" bit from my email address.