Re: Code Validation in CASE-generated CDE Application

From: Don Vick <dvick_at_lanier.com>
Date: Fri, 2 Dec 1994 15:48:03 GMT
Message-ID: <D06x84.FzA_at_lanier.com>

In article <3bli6q$3p4_at_newsbf01.news.aol.com>, Ted FC <tedfc_at_aol.com> wrote:
>We are using CASE 5.1 to develop an Oracle Forms 4.013
>application. The issue of handling validation codes has come up
>and we are having a debate within the project team. We would like
>input on how other sites are handling this situation.
>
>We all agree that small,static code sets like "gender code (M,F)"
>should be implemented in the database as check constraints.
>
>The disagreement occurs over situations where we have a code and
>it's name/description. One group thinks that each of these
>should be implemented as a separate table with a CASE-generated
>form to maintain the data. The other group thinks that codes of
>this type should be centralized in a generic codes table with a
>single generic codes maintenance form.
>
>The criteria for these code types are:
>
>1.) No attributes other than the key and a name or description.
>2.) More than a few (>6) discrete values.
>3.) Subject to routine maintenance (inserts,deletes).
> etc.

To quote an old TV commercial, "You're both right." Logically, there is a table for each kind of code. Physically it makes sense to put them into one table. But you can have it both ways. Make a table CODES:

	type	char(...),
	code	varchar(...),
	desc	varchar(...)

Then make views for each code type:
	create view xxx_codes as select code,desc from codes
	where type='XXX' with check option;

Queries, deletes and modifies can all be done through the views. I'm not sure about inserts, since the 'type' value has to be set. You may have to provide a procedure for this.

---

Donald E. Vick (dvick_at_lanier.com, dvick_at_crl.com) Voice: (404) 493-2194 Fax: (404) 493-2399 Received on Fri Dec 02 1994 - 16:48:03 CET