Re: Storing codes vs. human-intelligible values - best practice?
- From: Krzysztof Jamróz <SpAMkrzyh000JeST@xxxxxxxx>
- Date: Sun, 29 Jun 2008 19:02:52 +0200
On Sat, 28 Jun 2008 06:36:23 -0700 (PDT), dananrg@xxxxxxxxx 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
.
- References:
- Prev by Date: Re: Bug with ANSI/92 joins USING clause in Oracle 10g? Can't SELECT on join fields?
- Next by Date: Re: Storing codes vs. human-intelligible values - best practice?
- Previous by thread: Re: Storing codes vs. human-intelligible values - best practice?
- Next by thread: Re: Storing codes vs. human-intelligible values - best practice?
- Index(es):
Relevant Pages
|
