Home » SQL & PL/SQL » SQL & PL/SQL » Tbale with more than 2 foreign keys referencing to tables (Oracle 9.2.0.3)
Tbale with more than 2 foreign keys referencing to tables [message #429155] Mon, 02 November 2009 06:06 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
We are creating a set of tables in our application
There are 3 master table and the child table have more than 3/4 foreign keys in it. Can it be a good design in terms of performance?
Re: Tbale with more than 2 foreign keys referencing to tables [message #429158 is a reply to message #429155] Mon, 02 November 2009 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The model should be FIRST in 3NF, then you look at the treatments to know if some denormalization can improve performances.
FK cannot be a performance problem, there are needed so they are mandatory and so are out of the scope of optimization.

Regards
Michel
Re: Tbale with more than 2 foreign keys referencing to tables [message #429163 is a reply to message #429158] Mon, 02 November 2009 06:27 Go to previous message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir,
Below is the my design script.

CREATE TABLE EMPLOYEE
( 	EMP_ID  VARCHAR2(8) PRIMARY KEY,
	ACC_ID     VARCHAR2(6) ,
	FIRST_NAME   VARCHAR2(30) NOT NULL,
	LAST_NAME    VARCHAR2(30) NOT NULL,
	SEX       CHAR(1) NOT NULL,
	REMARKS VARCHAR2(50),
)   
/
CREATE TABLE EMPLOYEE_INFO 
( 	EMP_ID VARCHAR2(8) NOT NULL,
	DESIG VARCHAR2(3) NOT NULL,	
	EMP_JOIN_DATE DATE NOT NULL,
	EMP_LAST_DATE DATE NOT NULL,
	SALARY NUMBER(9),
	REMARKS VARCHAR2(30),
	FOREIGN KEY (EMP_ID)
    REFERENCES EMPLOYEE(EMP_ID))
/
ALTER TABLE EMPLOYEE_INFO
    ADD CONSTRAINT PK_EMPLOYEE_INFO
PRIMARY KEY (EMP_ID,DESIG,EMP_JOIN_DATE)
/

CREATE TABLE EMP_CONTACT_DETAILS
( 	EMP_ID VARCHAR2(8) PRIMARY KEY,
	CURR_ADD VARCHAR2(100) NOT NULL,
	PERM_ADD VARCHAR2(100) NOT NULL,
	CONTACT_NBR VARCHAR2(15),
	PAN_NBR VARCHAR2(15),
	UPDATE_TMS TIMESTAMP(6) NOT NULL,
	FOREIGN KEY (EMP_ID)
    REFERENCES EMPLOYEE(EMP_ID))
/

CREATE TABLE SKILLS
( 	SKILL_ID NUMBER(3) PRIMARY KEY,
	SKILL_NAME VARCHAR2(25) NOT NULL
)
/ 
CREATE TABLE EMPLOYEE_SKILLS
( 	EMP_ID VARCHAR2(8) NOT NULL,
	SKILL_ID NUMBER(3) NOT NULL,
	FOREIGN KEY (EMP_ID)
    REFERENCES EMPLOYEE(EMP_ID),
	FOREIGN KEY (SKILL_ID)
    REFERENCES SKILLS(SKILL_ID))
/
ALTER TABLE EMPLOYEE_SKILLS
    ADD CONSTRAINT PK_EMPLOYEE_SKILLS
PRIMARY KEY (EMP_ID,SKILL_ID )
/
CREATE TABLE APPLICATION1(
	APPLICATION_ID VARCHAR2(10) PRIMARY KEY,
	APPLICATION_NAME VARCHAR2(20),
)
/
CREATE TABLE TRAINING
( 	TRAINING_ID NUMBER(5) PRIMARY KEY, 
	TRAINING_NAME VARCHAR2(30) NOT NULL,
	APPLICATION_ID VARCHAR2(10),
	FLAG CHAR(1),
	FOREIGN KEY (APPLICATION_ID) 
    REFERENCES APPLICATION1(APPLICATION_ID))  
/

Previous Topic: SQL Select statement - Returning Dummy row if no results (merged)
Next Topic: To increment column value before insert
Goto Forum:
  


Current Time: Mon Sep 26 17:54:24 CDT 2016

Total time taken to generate the page: 0.26454 seconds