Home » Developer & Programmer » Designer » help me in denormalising my database
help me in denormalising my database [message #90581] Mon, 26 April 2004 07:39 Go to next message
Martin
Messages: 83
Registered: February 2000
Member
I have a database with the following tables

CREATE TABLE experiment
(
e_name varchar2(10),
e_date date,
creator varchar2(15),
site_of_creation varchar2(10),
target_substance varchar2(10),
description varchar2(20),
PRIMARY KEY(e_name)
);

CREATE TABLE experiment_version
(
e_name varchar2(10),
version_type varchar2(7),
description varchar2(20),
PRIMARY KEY(e_name,version_type)
);

 

CREATE TABLE compound_class
(
 class_name varchar2(10),
 description varchar2(20),
 PRIMARY KEY(class_name)
);

CREATE TABLE compound
(
  c_name varchar2(10),
  class_name varchar2(10),
  PRIMARY KEY(c_name),
  FOREIGN KEY (class_name) REFERENCES compound_class
);

CREATE TABLE result_type
(
result_type_name varchar2(10),
description varchar2(80),
unit_name varchar2(10),
PRIMARY KEY(result_type_name)
);

 

CREATE TABLE environment_variable
(
environment_id varchar2(5),
variable_name varchar2(15),
unit_name varchar2(15),
variable_value varchar2(10),
PRIMARY KEY(environment_id)
);

CREATE TABLE result_environment
(
result_id varchar2(8),
environment_id varchar2(10),
PRIMARY KEY(result_id,environment_id),
FOREIGN KEY(environment_id) REFERENCES environment_variable
);

 

CREATE TABLE result
(
result_id varchar2(8),
e_name varchar2(10),
version_type varchar2(7),
e_date date,
c_name varchar2(10),
result_type_name varchar2(10),
result_value varchar2(10),
significance varchar2(10),
PRIMARY KEY(result_id),
FOREIGN KEY(result_type_name) REFERENCES result_type,
FOREIGN KEY(c_name) REFERENCES compound,
FOREIGN KEY(e_name,version_type) REFERENCES experiment_version
);

 

now inorder to increase the performance of the database i am required to denormalise the above database such that the new one has  only TWO tables,

<LI class=MsoNormal>
<OL>
<LI class=MsoNormal>one for experiment and experiment version </OL>

    2. one for results (incl compund class, environment variables and result types with units)

please help me in this.....martin

Report message to a moderator

Re: help me in denormalising my database [message #90584 is a reply to message #90581] Mon, 26 April 2004 21:57 Go to previous message
lalitha
Messages: 39
Registered: June 2002
Member
Hi
I think the following structure can help u
CREATE TABLE experiment
(
e_name varchar2(10),
version_type varchar2(7),
description varchar2(20),
e_date date,
creator varchar2(15),
site_of_creation varchar2(10),
target_substance varchar2(10),
description varchar2(20),
PRIMARY KEY(e_name,version_type)
);

CREATE TABLE result
(
result_id varchar2(8),
environment_id varchar2(10),
variable_name varchar2(15),
unit_name varchar2(15),
variable_value varchar2(10),
e_name varchar2(10),
version_type varchar2(7),
e_date date,
c_name varchar2(10),
class_name varchar2(10),
description varchar2(20),
result_type_name varchar2(10),
description varchar2(80),
unit_name varchar2(10),
result_value varchar2(10),
significance varchar2(10),
PRIMARY KEY(result_id),
FOREIGN KEY(e_name,version_type) REFERENCES experiment_version
);

But my personnel suggestion is , this denormalization is not going to help u in performance improvement.
Use oracle's enterprise edition to resolve the complexities in the performance.
lalitha

Report message to a moderator

Previous Topic: Confusion :Normalization
Next Topic: Migration from oracle 8.0.6 to oracle 9i
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Feb 03 04:41:27 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.