Storing codes vs. human-intelligible values - best practice?
Date: Sat, 28 Jun 2008 06:36:23 -0700 (PDT)
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
-- 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"
-- 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.
-- I've always thought it was a best practice to store short codes
rather than longer human-intelligible values.
-- 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
Wondering if anyone with more experience than me (most people reading this group, probably) have any opinions on the best way to proceed. What other factors do I need to consider in making a choice?
(A few project constraints that may or may not be relevant to my
question (dictated to me, so they're non-negotiable) are:
-- Can't use check constraints (and wouldn't want to; easier to update
values in a domain table than change a constraint)
-- Can't use Oracle product-specific domains; we're typically asked
not to use vendor-specific features)) Received on Sat Jun 28 2008 - 08:36:23 CDT