Relational database architecture

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

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 Received on Tue Jan 28 2003 - 00:04:36 CET