Re: Relational database architecture

Views. Definitely views. All apps can use a view. That will automatically cache that static look up table for you. Jim

--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Tim Smith" <timasmith_at_hotmail.com> wrote in message
news:a7234bb1.0301271504.1d6c7930_at_posting.google.com...


> Hi,

>    Assume a large enterprise database with the following common theme:

>

> table_data

> pkey  person_type         sex etc

>    1          50          175

>    2         100          175

>    3          50          200

>

> static_lookup

> pkey_code  short_description set

>        50  customer          person type

>       100  employee          person type

>       175  male              sex

>       200  female            sex

> etc

>

> If I select from table_data I see the above.

> If I want to see the values for the codes I need to do:

> select sl.short_description as person_type,

>        sl2.short_description as sex

> from table_data td, static_lookup s1, static_lookup s2

> where td.person_type = sl.pkey_code

>   and td.sex = s2.pkey_code

>

> This type of access is repeated throughout all applications.

>

> My question is how to optimize this efficiently.  Ideally I want the

> application designers to be able to do

> select * from view_data

> and get both the code and the description.  The former for filling out

> data structures and the latter for user display.

>

> In the past I have seen this done in the middleware which has a cache

> of the codes and fill out data structures as requested.  However this

> only works for the apps and is less usefull when you want to do direct

> queries against the database (typically by users).  It is however

> extrememly efficient - faster than ever joining to each table in a

> query.

>

> Is there some way of pinning the table in memory and using stored

> procedures and views - will it work as well?

>

> thanks!

>

> Tim