> various disagreements and "quotes"...
I agree that you disagree.... :)
RECURRINGCHAR does not break normal form. It simply optimizes the storage
of reference values (recurring keys). This allows for the use of 'long
words' as reference values with a great deal of system storage savings and a
boost in performance in certain circumstances. This is more a form of
'compression' then anything else, as a matter of fact, this is very similar
to the LZ78 family of substitutional compressors.
http://www.faqs.org/faqs/compression-faq/part2/section-1.html
The advantage here is that we are targeting a normalized value in it's
atomic state, The recurrence rate of this these values is extremely high
which allows us to store this data in a very small space and optimize the
access to this data by using the 'dictionary' that we create.
>What if tomorrow you will need to change text name for "OPEN" status to
>"OPEN_PENDING_SOMETHING"? With your design, you will need to update all
>rows in the table changing it. With normalized design, you just update the
>lookup table. Etc, etc.
In either model you would:
update master_table set status='OPEN_PENDING_SOMETHING' where status='OPEN'
This would not change, in fact, even in a normalized design you wouldn't
change the lookup table (parent) key. Perhaps you are misunderstanding my
initial concept. The MySQL 'enum' is close. However, it is static and
requires you to embed business data (your key list) in the DDL. The idea I
have here is to dynamically extend this list as needed. I am not saying
that the value can't relate to a parent (lookup) table. It's just not
necessary if the value is all that is needed.
--Dave (Hoping some other SQL developers are monitoring this thread :)