Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Setting parameters at Hierarchy within a hierarchy

Re: Setting parameters at Hierarchy within a hierarchy

From: Lennart Jonsson <lelle2_at_bonetmail.com>
Date: Tue, 04 Mar 2003 21:21:24 +0100
Message-ID: <b431p9$1qlcqn$1@ID-167942.news.dfncis.de>


On Sun, 02 Mar 2003 10:46:51 +0000, hs wrote:

> All,
> I have a situation where I have two hierarchies : the Oragnization
> hierarchy which has 6 levels, for example
> Firm
> Entity
> Business_Unit
> Division
> Region
> Office
>
> and the second hierarchy is
> Business_Group
> Product_Group
> Product
> Sec_Type
> Sec_Sub_type
>
> I have resolved the above two hierarchies by creating 1 table at each
> level and setting up a 1:M relationship.
>
> From a processing perspective, the business rules requirements are to
> be able to set parameters at
> Firm
> Business_Group
> Product_Group
> Product
> Sec_Type
> Sec_Sub_type
>
> Entity
> Business_Group
> Product_Group
> Product
> Sec_Type
> Sec_Sub_type
>
> and so on ..... all the way down to the account level. Parameters set
> the lowest level will override the values at a higher level. No matter
> where the parameters are set, the requirements is to be able to see
> the values at the ACCOUNT - SEC_SUB_TYPE level.
>

I might have completely missunderstould your problem, but here goes my thoughts:

I assume you want to create relations with attributes between:

  1. Firm - Entity
  2. Firm - Business_Group ...
  3. Firm - Sec_Sub_type
  4. Business_Group - Entity ...
  5. Business_Group - Sec_Sub_type ...
  6. Sec_Sub_type - Sec_Sub_type

If this is true, you could create a "supertype" for each hierarchy and create the relation between the "supertypes". I.e. something similar to:

create table Organization (

	id integer not null,
	type integer not null,
	primary key (id, type),
	check (type between 1 and 6)

)

create table Firm (

	firmid integer not null primary key
	type integer not null default 1,
	foreign key (firmid, type) references organisation
	check (type = 1),
	...

)
...
create table Sec_Sub_type (
	sec_sub_type_id integer not null primary key,
	type integer not null default 6,
	foreign key (sec_sub_type_id, type) references organisation
	check (type = 6)

)

and similar to the other hieracy. The create the relation where you put your parameters between Organization and the other supertype. Like I mentioned, I am likely to have missunderstould your problem, but it might anyhow give you some ideas.

/Lennart

[...] Received on Tue Mar 04 2003 - 14:21:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US