Re: Storing codes vs. human-intelligible values - best practice?
Date: Mon, 30 Jun 2008 10:19:24 -0700 (PDT)
On Jun 28, 6:36 am, dana..._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
> -- 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))
I would be concerned where I see "integer value" and "orders of magnitude more rows" referring to the same thing.
I would be very concerned when I see things like columns called "column name." This likely refers to some report generator or data dictionary replacement. If you are doing that, you almost certainly want it to be human readable. The performance will suck anyways, so you won't likely gain anything with shorter codes, and you will need to fix problems. You don't want to bother with limitations that come from the days of 121 segment limits.
I work with database-agnostic enterprise software every day, I'm not just dissing it to be nasty. It has its place, but if I were developing a new product I would have parallel code for db-specific differences. Some things just plain work different.
-- @home.com is bogus. http://upcoming.yahoo.com/event/164730/Received on Mon Jun 30 2008 - 12:19:24 CDT