Re: design question, number vs string datatype

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 12 May 1998 01:19:42 GMT
Message-ID: <3557a30a.2554623@192.86.155.100>

A copy of this was sent to Sean Hull <shull_at_panix.com> (if that email address didn't require changing) On Mon, 11 May 1998 16:43:47 -0400, you wrote:

>
>Hello all:
>
>I have a fairly simple situation. I have demographic data like this:
>
>< 10,000
>10,000 - 20,000
>...
>> 100,000
>
>Along with other info like the individual's name, ssn etc. I would like
>to store this info in a table, but obviously I don't want to store the
>values above as strings. I'm planning to store them as number(2) in
>Oracle. That leaves 0-99 possible values for this field.
>
>My question: Is this how others store this type of information? Do you
>leave the translation back from the db value to a range string up to the
>application code? Or do you store these translation values in another
>table like this:

Always in the database and in one of 2 ways:

1- lookup table as you described
2- as a decode in a view, for example:

     select decode( id, 0, '< 10,000', 1, '10,000 - 20,000', ..... ) Name
       from t

#2 is the way the data dictionary works in general. for example:

SQL> select text from all_views where view_name = 'ALL_TAB_COLUMNS';

TEXT

---

select u.name, o.name,

       c.name,
       decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                       2, decode(c.scale, null,
                                 decode(c.precision#, null, 'NUMBER', 'FLOAT'),
                                 'NUMBER'),
                       8, 'LONG',
                       9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                       12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
                       69, 'ROWID',
                       96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
                       105, 'MLSLABEL',
                       106, 'MLSLABEL',
                       111, ot.name,
                       112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
                       113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                       121, ot.name,
                       122, ot.name,
                       123, ot.name,
                       'UNDEFINED'),
         ...............

>
>income_trans table
>
>id name
>-------------------
>0 '< 10,000'
>1 '10,000 - 20,000'
>...
>9 '90,000 - 100,000'
>10 '> 100,000'
>
>And then do a table lookup? I guess most queries will be how many people
>fall into each category, so the lookup would only have to be done once.
>Also, I believe bitmapped indexes would be appropriate for such a field
>correct? That is if the # of unique values was .01% or less.
>
>Any comments?
>
>Thanks,
>Sean
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon May 11 1998 - 20:19:42 CDT