Re: Relational database architecture

Do I have to pin the table in memory though, I don't see how a view along is going to cache the table.

Tim

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:<VdmZ9.68695$AV4.3381_at_sccrnsc01>...
> 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
Received on Tue Jan 28 2003 - 20:39:10 CET