Re: Data Modeling Question

In article <7qoq4t$cj6$1_at_demon.uunet.ca>,   "Ioan Berbece" <iberbece_at_servicesoft.com> wrote:
> 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.

I think you are right. Your original question stated "suppose you had table TA and TB". If that is no longer a constraint, then just have one table, not three. The one table would be like your TH table. The table would obviously have to all the attributes of both A and B.

If the entities A and B both had a large number of attributes most of which were not shared then having three tables at the physical level would be a reasonable implementation. At the logical level it would still be one entity sub-classed into two.

What is the purpose of your type column? Is it to distinguish rows beloning to entity A from rows belonging entity B? If it is then you do not need it. Entity A rows have a NULL parent_id. Entity B rows always have a parent. So you can already distinguish them.

If the type is nothing to do with A and B then on your physical TH table I would suggest that take the type column out of the primary key. You would still make the type column a NOT NULL column. If you do this then you will be able get rid of the parent_type column. This will make specifying the join condition in SELECT statements simpler and it will make the statements run more efficiently.

(I assuming here that id is just an arbitary number that you assign when you load the data into the table, rather than coming from some external source of data over which you have no control.)

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

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Sep 03 1999 - 18:13:55 CDT