Help on Data Integrity Constraints

From: <vsatya_at_gmail.com>
Date: 3 May 2005 12:07:44 -0700
Message-ID: <1115147264.187089.111610@g14g2000cwa.googlegroups.com>

Suppose I have a table "Master" with 3 fields Table Name: "Master"
Columns:

Type Varchar2(10)
Code Varchar2(5)
Desc Varchar2(20)

Combination of Type & Code is the Primary key.

Let's say this table acts like master/setup data to different constants that will be used in the system.

Sample Data:

Type Code Desc

---

AISTATUS	A		Active
AISTATUS	I		InActive
ROLE		MGR		Manager
ROLE		DIR		Director
ROLE		SRMGR		Senior Manager
SEX		M		Male
SEX		F		Female

.....
.....

Values of the "Code" are used to store in other tables like below

Ex:
TableName: TableA
columns:

EmpId	Varchar2(5)
EmpName	varchar2(10)

Status Varchar2(5) -> Possible values are A,I and is determined by AISTATUS records of the above table
Gender Varchar2(5) -> Possible Values are M,F and is determined by SEX records of the above table
Designation Varchar2(5) ->Possible values are MGR,DIR,SRMGR and is determined by ROLE records of the above table

Which one of the below approachs is the best way to maintain referential(data integrity) constraint between these two tables? 1. Use triggers to validate the data.
2. Add 3 more columns in this case to the table and add 3 foreign key reference constraints to Master Table.
Updated TableA definition:

EmpId		Varchar2(5)
EmpName		varchar2(10)
StatusType 	varchar2(10)
Status  	Varchar2(5)
	Foreign key on StatusType,Status referencing Master(Type,Code)
GenderType 	varchar2(10)
Gender		Varchar2(5)
	Foreign key on GenderType,Gender referencing Master(Type,Code)
Des_type 	Varchar2(10)
Designation 	Varchar2(5)
	Foreign key on Des_type,Designation referencing Master(Type,Code)

Any ideas/help on this highly appreciated.

Thanks Received on Tue May 03 2005 - 14:07:44 CDT