dynamic naming for hierarchical problem

[Jeremy Jones]

Alex Martelli <aleaxit at yahoo.com> wrote in message
news:9l409801oli at enews4.newsguy.com...

OK.  Time for humble pie....

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

I quickly stand corrected (as I anticipated).  This is EXACTLY the type of
thing that I KNEW that I was going to overlook and get called on.  Again, I
really appreciate the type information that you folks repeatedly are giving
me here.  It is obvious that I am not thinking nearly as clearly as you guys
are about this.  Again, perhaps it is my "greeness" with actual database
schema design that has put the blinders on my eyes.  Let me explain the line
of thought that I initially thought that you were pursuing:

One table will define the relationship between children and parents (this
one has already been laid out before by someone).
Another table (that I think you suggested) would link the attributes back to
the node that it was describing.  My thoughts were (and this is really,
really dumb now that I think of it) that each record in the first table
would have one and ONLY one record in the second table.  The second table
would have to have multiple ATTRIBUTE columns, the same number as the
maximum attributes for any particular node.  Now, this is really stupid for
me to have thought along those lines for a couple of reasons: 1) because if
I were to create a schema like that, then it would be pointless to have the
second table.  Since there would be a one-to-one relationship between table
one and table two, why have table two?  Why not just tack it on to table
one?  2) It doesn't take full advantage of the relational nature of
databases (which you are so graciously schooling me in - and I am humbled by
my lack of knowledge in the matter).  So, now that I have exposed my
ignorance to the world.....

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

....this is the solution that I could not see the forest for the trees on.
This would definitely work.  And would actually be a fairly simple desing to
work with.  And would not be a bear to extend like I originally thought,
because it appears flexible enough to handle as many different attributes
and types of attributes that I could dream of.  But I am still not sure I
will use it.  I will explain why below.  But I just want to say, again, how
utterly grateful I am for all of the posts for the primary reason that you
folks are stimulating me to think in ways that I am not accustomed to
thinking.  Now that I see what you are saying above, it is obvious and it is
a simple solution.  But I was completely oblivious to it.  Have you ever
heard the saying, "What you don't know, you don't know that you don't know"?
(One more "don't know than absolutely necessary, but it emphasizes the
point.)  You opened up something that I was ignorant of and for that I am
sincerely grateful.

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

Point very well taken...it will be a good thing to keep in mind for future
projects.

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

I will probably use XML for a number of reasons:

1)  All of the data that I plan on using should easily fit into memory.
2)  The data that I plan on using fits nicely into the framework that XML
provides.
3)  (And this one is primarily for Peter Hansen, to answer his statement on
picking something simple and getting it working)  XML may not be the
simplest approach for this project (if only from the standpoint of the
learning curve).  I could use stuff that I am already more familiar with
rather than learning XML and how to walk down a DOM tree; and I could
probably put together a set of scripts with the familiar technology more
quickly.  And I would probably (as Peter pointed out) be following good XP
rationality by doing so.  However, I think that XML fits in nicely for a
future project that I have in mind.  I know that this violates XP, but I
have that flexibility with the project that I am working on right now.  So,
for my technological growth and for learning something for a future project,
I will probably go with XML.

Again, thanks for all the info and I appreciate the patience that you folks
have had with me....time for a big helping of humble pie....