Re: Hierarchy Dilemma

On Wed, 11 Dec 2002 09:20:05 +0000, hs wrote:

> Here is a synopsis on the problem that I am struggling with:
>
> 1. I have two hierarchies: Account, which has the firm, linked to the
> division which in turn links to Office and fineally Account.
> Graphically,
>
> Firm
> Division
> Office
> Account
>
> These are all separate tables and are a 1:M relationship.
>
> 2. Second hierarchy is the Security hierarchy
> Product_Group
> Product
> Security_Type
> Security
>
> Again they are separate tables with a 1:M relationship
>
> Now I have to design a table that will hold attributes at different
> levels acroos the two hierarchies.

[...]

One idea is to use a "supertype" for each hierarchy. I.e. something like

create table orgunit

	id integer not null,
	unittype integer not null,
	<common data, name for example?>
	constraint pkorgunit primary key (id)
	constraint akorgunit unique (id, unittype)

create table firm (

	id integer not null,
	unittype integer not null default 1,
	<firm data>,
	constraint pkfirm primary key (id),
	constraint akfirm unique (id, unittype),
	constraint fkfirm foreign key (id, unittype)
		references orgunit (id, unittype)
		on delete cascade,
	constraint chkfirm check (unittype = 1)

create table division (

	id integer not null,
	unittype integer not null default 2,
	firm integer not null,
	<division data>,
	constraint pkdivision primary key (id),
	constraint akdivision unique (id, unittype),
	constraint fk1division foreign key (id, unittype)
		references orgunit (id, unittype)
		on delete cascade,
	constraint chkdivision check (unittype = 2),
	constraint fk2division foreign key (id)
		references orgunit (id)
		on delete restrict

etc. Then do something similar for the other hierarchy and you can create your relation between orgunit and the other "supertype". The obvious drawback is that you will have to do an extra insert in the "supertable" whenever you add something to one of the "subtables"         

Just an idea, but it might give you some ideas

HTH
/Lennart            Received on Thu Dec 12 2002 - 00:31:47 CET