dynamic naming for hierarchical problem

[Alex Martelli]

"Jeremy Jones" <dnjjones at yahoo.com> wrote in message
news:vHcd7.141319$um3.1177136 at typhoon.jacksonville.mediaone.net...
    ...
> > for a parent/child relationship.  Rather, since children appear to
> > have varying attributes, you'll need to split THOSE off into a table
> > of their own (with CHILD as a foreign key).
>
> In the example that I was working on, the children (not of the same
parent,
> but in different tiers of the hierarchy) were of differing types and would
> have different attributes and different quantities of attributes.  I don't

Yep, that's what it seemed like from your example, just as I said
and you quoted.

> result.  I suppose you could hack together a table that would accomodate
all
> of those different attributes and have CHILD as a foreign key, but it
seems
> it would be a pain to give it room to grow if I needed it to.  (I just
KNOW

How tables grow is an administrative matter of the RDBMS you're using --
some make it a pain (because they want to offer you, the DBM administrator,
lots of possibilities for performance &c -- "Enterprise-class" RDBMS, used
to manage LOTS of gigabytes), some make it totally transparent and still
manage to deliver decent performance for less-humongous data sets (you
*aren't* going to have all that many gigabytes here, are you?).  I would not
worry about that at this stage.  Organizing one table of attributes (or N+1,
if you want attributes of N different types) in this manner is no "hack" --
it's
standard operating procedure for relational DB design.  E.g. say all of your
attributes have a string name and string contents, one attribute always
belong to just one child (many-to-one, not many-to-many), then what you
want is a table with three fields: ATTNAME (attribute name), CHILDID (the
ID of the child to which the attribute belongs), ATTVALUE (attribute value).
CHILDID is indeed a foreign key, and ATTNAME+CHILDID the primary key
of the attribute table (eschew nonstandard RDBMS which don't let you
have foreign keys as part of primary keys... but if you're stuck with one,
just add the usual semi-hack, a unique attribute-id per row of this table
to act as the primary key -- technically violating a normal-form constraint,
but then that's the price you pay for using a nonstandard RDBMS:-).

> that I am setting myself up here for you guys to show me some dynamite way
> to accomodate a dynamic set of varying numbers of attributes since it is
> obvious that both of you have worked a lot more with databases than I
have.
> When you prove me wrong, I will gratefully accept the humiliation  :-)

It's really as simple as I just said -- relational databases ARE a pretty
powerful concept, you know.  Whatever you end up using for this
project, I heartily suggest you plan to get some RDB design ideas
in the future: a well-designed RDB scales up enormously (with the
right underlying technology -- I'm not talking of abusing the Jet
engine, aka 'access DB', or Gadfly, for an Enterprise-scale project,
but of reusing a logical DB design born for a small engine 'up' with
an Enterprise-class engine such as DB2, Oracle, or SQL Server).

Sure, sure, XML *IS* great -- whenever you need to exchange (and
particularly transmit) information between machines, XML's the
cat's pajamas.  But for storing large datasets locally, XML textfiles
may not be the best choice -- store them into a RDBMS, which when
well managed will give you a zillion advantages, and if need be use
the appropriate utilities to dump out a part of your DB in XML form
and/or import XML-form additions.


> I appreciate the posts.  They have definitely spurred me on to think
> differently.  I am still inclined to think that the DOM/XML route will be
a
> more simple route for my specific problem.

This is quite possible -- you do get a handy in-memory format,
hierarchical just as you need, rather than needing any conceptual
translation step hierarchy <-> relational.  If the amount of data
you're going to handle in one step will comfortably fit in memory,
and you have no need for other typical RDBMS benefits such as
transactional integrity and data-independence (ability to easily
write other apps in the future that exploit the same data in a very
different access pattern), then XML may well be the way to go.

But that's not because there's any intrinsic difficulty in mapping
your problem to a relational pattern...!-)


Alex