RE: Lookup table design thoughts needed

I would start by considering how the application is deployed.  For code that is easily deployed (E.I.. executables are located on a few application servers or a shared drive) I would consider compiling the rule data along with the logic.  It generally improves run-time performance because there is no database access, network traffic, etc.  Initialization of the bloated executable would be offset by the run-time savings.  You incur a penalty when adding a new codes since you have to recompile and deploy the new executable but itsanotsobad in an app. server/shared drive deployment.

Another consideration to take is the frequency that rules may change.  Your example for state codes has remained the same since the 50's.  I would pursue the hardcoded route if the code tables are more or less stable, or if the changes in the rules would involve code changes anyway.

In a 2-tier client-server deployment I would go the database route, although separate tables for each type of code.  This will simplify code table changes by not requiring a redeployment of the compiled code.

Tony Aponte

-----Original Message-----

From: Tracy Rahmlow [mailto:Tracy.Rahmlow_at_aexp.com]

Sent: Wednesday, December 05, 2001 12:15 PM

To: Multiple recipients of list ORACLE-L

Subject: Lookup table design thoughts needed

We are currently looking at rewriting our entry system and one issue that I am

looking for some feedback involves the use of lookup tables and

populating/editing screens.  We are looking at creating a generic table that

contains all the valid entries for each drop-down list.  For example, we may

display a list of valid states for the user to select.  The proposed "edit"

table contains a row for each state with the following columns as an example:

table_name: address

column_name: state_cd

code: WI

description: Wisconsin

In addition, we have situations on the screen where a user may select option

'a' in a drop-down list, but can not choose option 'c,d or f' in a different

drop down list.

Any suggestions for designing  a flexible system that would incorporate issues

like the above.  We have been considering either "hard-coding" the edits within

the screen as well as creating a "rules/validation" table that would

incorporate these edits.  How practical is a rules table? (We do have

situations where we may have multiple entries to validate to each other).  I

realize these are very broad questions, so I am looking for generic theories

that may be applied that are flexible for adapting to changes within the

business.  What else should I consider?  It appears as if there are several

ways to skin the cat how do we go about choosing the best method for our

situation.

 In addition, does anybody know of any good websites/books that contain

relational design strategies, tips ...

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com

--

Author: Tracy Rahmlow

  INET: Tracy.Rahmlow_at_aexp.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California        -- Public Internet access / Mailing Lists

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). Received on Tue Dec 11 2001 - 11:52:59 CST