Re: Setting parameters at Hierarchy within a hierarchy

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),
	...

	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 - 21:21:24 CET