ATTN: VSATYA RE:Help on Data Integrity Constraints ATTN: VSATYA

From: Matt <none_at_anon.com>
Date: Thu, 12 May 2005 02:12:05 GMT
Message-ID: <VXyge.15296$3b4.13999@twister.nyroc.rr.com>

VSATYA, Here is my solution to your Data integrity issues. I think you should in essence normalize your Master table to something as described below....

Sample Data:

Master Table

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

I would break the Master Table into these three tables.

In this way you would only need a primary key constraint on CODE. I know you probably dont want to go breaking up that master table up but this will most likely save youn a lot of hassle in the future. Mainly in the ease you will have adding new values to the MasterRoles_table and not having any type of conflicts.

---

MasterRoll_table;
Code Desc

---

DIR            Director
MGR            Manager

.
.

sysadmin System Administrator **this table will surely grow as time passes**

MasterSex_table;
Code Desc

---

M                Male
F                Female


MasterAISTATUS_table;
Code           Desc
---------------------
A	    Active
I	    Inactive

***********************************************

Now by having done this when you go to update Table A you will not need to add those extra columns.

Ex. Data

TableA

Emp_id EmpName Status Gender Designation

---

999999 endUser A M Director
.
.
.
.

911              theDBA                  I               M                       theDBA      **Record not added Since 'theDBA'
						 is not in ( SELECT desc FROM MasterRole_table)***

Ex:
TableName: TableA

columns:

EmpId	Varchar2(5)
EmpName	varchar2(10)
Status  Varchar2(5)
Gender	Varchar2(5) 

Designation Varchar2(5)

  Foreign key on Status referencing MasterAISTATUS(Code) <---so long as the value is in the column it will be added   Foreign key on Gender referencing MasterSex_table(Code) <---so long as the value is in the column it will be added   Foreign key on Designation referencing MasterRoll_table(Desc) <---so long as the value is in the column it will be added

Original Post

---

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

--------------=  Posted using GrabIt  =----------------

------= Binary Usenet downloading made easy =--------- -= Get GrabIt for free from http://www.shemes.com/ =- Received on Wed May 11 2005 - 21:12:05 CDT