Re: Data modeling question about reference table

Stephane,

Sounds like you know the answer really.

If your reference tables are all like (CODE, VALUE) or similar, and they are pretty static values, why not move them all into one table? Probably gives you performance advantages and maybe code reuse:

SELECT value
FROM ref_values
WHERE code = :bind_value

Instead of:

SELECT value
FROM <code_table_name>
WHERE code = '<code>'

Whether its really worth the bother depends upon your precise requirements, data volumes etc though. (Could be that most of the reference tables are not used much. By caching the few that are used, you can get great performance without indexes... like I say, just depends.)

Cheers,

John Thomas

In message <F001.004DE542.20021002082340_at_fatcity.com>, paquette stephane <stephane_paquette_at_yahoo.com> writes
>Hi,
>
>We're discussing on reference table.
>One containing everything (using a type) or one per
>entity. We'll have a lot of entities.
>
>This is for a staging area where data will be validate
>before going in Siebel. In theory, this staging will
>become a very big staging for a datarehouse and still
>in theory there is no plan yet that that staging will
>be available to the users as an ODS.
>
>What do you think ?
>
>=====
>Stéphane Paquette
>DBA Oracle, consultant entrepôt de données
>Oracle DBA, datawarehouse consultant
>stephane_paquette_at_yahoo.com
>
>___________________________________________________________
>Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
>Yahoo! Mail : http://fr.mail.yahoo.com
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 
John Thomas                                     Tel:    01506 881 037
Oracle Contract DBA                             Mobile: 07986 182 368
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Thomas
  INET: oracle_at_toronto.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).