Re: Storing codes vs. human-intelligible values - best practice?

From: Krzysztof Jamróz <SpAMkrzyh000JeST_at_ZLYo2.pl>
Date: Sun, 29 Jun 2008 19:02:52 +0200
Message-ID: <3yyma51rhvqf.21nffx6yanzh$.dlg@40tude.net>


On Sat, 28 Jun 2008 06:36:23 -0700 (PDT), dananrg_at_yahoo.com wrote:

> In a pre-existing project, many tables reference domain (lookup)
> tables of the general structure:
> # COL_NM (column name) -- a primary key containing a human-
> intelligible value; max varchar length varies by table, but typically
> no longer than 30 characters
> * COL_CD (column code) -- a not null field with a unique
> constraint, typically an integer with a length of 1-2 digits.
> o COL_DES (column description) -- an optional description field;
> could be up to 255 chars
>
> The child tables referencing these domain tables use the COL_NM human
> intelligible values rather than the less storage intensive COL_CD
> (since it is unique and not null, it could also be used by a
> referencing child table).
>
> In a new project, it's been suggested to me that I continue to store
> COL_NM values rather than COL_CD values in the child tables with these
> rationales:
> -- It's better to use COL_NM because, for anyone directly querying the
> child tables, COL_NM means something to a human. COL_CD doesn't
> (unless someone has a really good memory).
> -- "Oracle can handle it"
>
> However:
> -- The new child tables will have orders of magnitude more rows than
> the child tables in the old database; so, although "Oracle can handle
> it", storage may be an issue, etc.

How large is the increase of size? 10 or 20% may be or may not be a big deal.

> -- I've always thought it was a best practice to store short codes
> rather than longer human-intelligible values.

I think, that short, text, mnemonic codes are quite convenient, eg: X -> "Order of type X"
SPEC_B -> "Special order of type B"
etc.

You do not have to memorize nor print numeric codes to understand query results, but the codes are still not very long. However, you will need a join to get nice value, that you can display for the user.

> -- As far as I can tell, few if any users will be querying the child
> tables directly; and if they were, they could join to the domain table
> to get the human intelligible values

I think, that you are the one, who will be querying the tables directly the most :) - during development and maybe bug fixes.

-- 
Krzysztof Jamróz
Received on Sun Jun 29 2008 - 12:02:52 CDT

Original text of this message