Re: Data Modeling Question

OK. Having this model implemented, how would you go about retrieving a branch of the tree? Suppose I have the primary key of a node (and its type, obviously) and I want to retrieve the tree that has that node as its root. Let's assume we work with Oracle or we have a mechanism similar to CONNECT BY which is capable to retrieve a subtree starting from a node. My point here is this: Wouldn't it be more efficient to have the hierarchy stored separately from the tables TA and TB, let's say TH like below:

CREATE TABLE TH
( ID NUMBER(9),
  TYPE CHAR(1),
  PARENT_ID NUMBER(9),
  PARENT_TYPE CHAR(1),
  CONSTRAINT TH_PK PRIMARY KEY (TYPE, ID),   CONSTRAINT TH_FK FOREIGN KEY (PARENT_TYPE, PARENT_ID) REFERENCES TH (TYPE, ID)
)

Please correct me if I'm wrong.

Ben Ryan <benryan_at_my-deja.com> wrote in message news:7qmqge$5ta$1_at_nnrp1.deja.com...
> In article <7qmg04$bs9$1_at_nntp3.uunet.ca>,
> "Ioan Berbece" <iberbece_at_servicesoft.com> wrote:
>
> One way would be......
>
> Assuming both tables have single column primary keys defined.
>
> Table TB would need two optional (nullable) columns the first
> with a foreign key constraint on table TA and the second with
> a foreign key constraint on itself. In addition you would
> need a trigger on TB to ensure that one, and only one, of the
> two columns had a value.
>
> Is this elegant? That is up to you.
>
> > Hello,
> >
> > Suppose I have a table TA which represents entity A and a table TB
> which
> > represents entity B. Between entity A and entity B there is a
> hierarchical
> > relationship meaning an object of type B can have an object of type A
> as its
> > parent. At the same time entity B has a hierarchical relationship with
> > itself. The two relationships are exclusive. Something like this:
> >
> > A1
> > |.....B1
> > |......B11
> > |......B12
> > B2
> > |.....B21
> > |......B211
> > |......B212
> >
> > How can this be modeled in the database in an efficient manner and
> > exploiting as much as possible the database built-in features (RI,...)
> and
> > having an elegant solution (if at all possible)?
> >
> > Thank you.
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Fri Sep 03 1999 - 10:44:36 CDT