Embededed lookups in a column

I have been given the project of coming up with a denormalized version of our corporate database. The main database is ultra-normalized and comples, therefore it is very difficult to use for smallscale apps that only need to get to part of the data. The schema design is going well, but I have a sitution that I would like to resolve if I can.

The datbase relies heavily upon lookup reference tables. Many codes are stored in the main data tables which have to be crossmatched with longer :name" values in reference tables. The code values are very stable, the lookup values are constantly changing. So it is not a real good idea to store the name values in the main tables, since these would have to be updated so often as to add too much processing time and confusion. So the standard solution is to do lookups to the reference tables from the code values.

But----I need to make this database easier to deal with for users with lower SQL and programming skills. Having SO MANY code lookups seems overly cumbersome. So I was wondering if ORACLE offers any tricks we can use. I was hoping that there would be some way to embed a query in a column defintion. The user would refer to the this column by its regular name, but ORACLE would provide the lookup name value in the background. Is this sort of thing possible? Also there is the catch that the name values associted with a particular row in a table have to be indexed.

Right now my company has some databases on ORACLE 7, some on 8i. I think the final incarnation of my database will finally be on some version of 8i, if this helps to provide more options.

Thank you for any suggestions you can provide.

Bart Torbert
bart.torbert_at_ihsenergy.com Received on Tue Jan 15 2002 - 10:04:03 CST