Re: Database design

From: Mark Johnson <102334.12_at_compuserve.com>
Date: Fri, 24 Feb 2006 17:14:52 -0800
Message-ID: <kt9vv1daei76qltqdtajuelvv63kr6mufs_at_4ax.com>


mAsterdam <mAsterdam_at_vrijdag.org> wrote:

>Mark Johnson wrote:
>> mAsterdam wrote:

>>>>That is, the tables are supposed to correspond to relations in RM.

>>>You could have tried a different phrasing. I'll just
>>>guess an interpretation.

>> No need for that. Take it from Codd:

>> Rule #2: "table name, primary key value, and column name"

>> Rule #1: "values in tables"

>> Perhaps you would argue that he meant "relation" to mean something
>> more than one table?

>Codd used the word "table". I knew that.

I wasn't sure. The only way would be to go to the local library and copy the actual magazine article or any follow-up paper, keeping in mind any change in Codd's opinions (as we all change our opinions). I do know it's often represented, this way. But I would also suspect that the same 12 Rules would read a bit differently if the source were Date, Darwen, etc.

>Codd used the word "table". I knew that.

oaky

>>>Yes. This holds for structures of buildings, cells,
>>>musical compositions, and other systems.

>> Not necessarily. A musical composition can be modified by simple
>> insertion, either by Mozart or Lars of Metallica before his cut n
>> paste machine. Another chorus can be added, the entire tempo changed
>> at points with a few 'marginal' marks on the score, etc.

>Yes. Copying things, and copying parts of things is possible.

Or even just marginal notes, since the concern was for difficulty in making changes.

>> That structure of tables and links is as inflexible as a COBOL
>> hierarchy, in other words, in terms of later modification. I thought
>> you had agreed.

>In a general sense, yes. Something like this:

>Structure supports substance. Structures tend to get less flexible
>over time. As such they become an obstacle for change over time:
>by changing a structure you risk what it is supporting.

Yes. You risk not knowing, because in the real world, it's not just one man or woman behind the design, or any ill-considered object programming tweaking in obscure subroutines. Even a simple change to the scheme could introduce errors that took a week to uncover.

>> But such inflexibility is not found in a tree
>> structure, suggested by the need to use explicit/full/materialized
>> paths, to which branches and leaves can be added, literally at whim.

>I don't know if a hierarchy is inherently more or less
>flexible/stable than a mesh.

It's more flexible in terms of unanticipated modification. But there are important considerations addressed by database builders having to do with integrity (floating, loose records), and simply the rate at which queries can be performed. That was the reason for many looking to an explict/full/materialized path for such tables which otherwise violate the basics of the RM. The idea was that tables derived from the RM would not be ordered. But these hierarchical tables are explicitly ordered and by self-referential links. It's even worse than mere 'storage order'. And if one were to suggest - but wait, it's basically a bunch of normalized tables all overlayed on the same logical table for convenience, and because it lends itself easily to automated programmatic control - they'd probably be having none of that, particularly because one is not constrained to view any particular list, but could pick any tree, or the table as a whole, intentionally or by mistake.

Proponents of the RM against such schemes would certainly argue that they only have in mind the reduction of confusion, of error, and an increase in the speed and simplicity of retrieval of the data by whatever ordering. And if Connect By is burdensome or slow, even just in that they have a point, never mind that it is proprietary. Perhaps if it were as easy to transparently create tables without overhead or explicit design, as it is to add subtrees and leaves to a hierarchies, it might be less of an issue. But even in a well-known organization, with few anticipated changes in structure and order, surely some things come up, if just forced on them by changes made by large clients or vendors. I think that's why Oracle does have a Connect By, in the first place. And I'm sure many companies get a lot of use out of it.

>>>what you /choose/ to see as primitives or combined primitives.
>>>If you can't decide on that, you can't design a database.

>> But it's done all the time. People do combine primitives into larger
>> constructs. These in turn become primitives to others, and so on.

>What makes you think that I think this doesn't happen?

But:

>("/But/ it's done all the time.")

You don't agree that this is common?

And:

>> have used the term, blocking, to describe this pattern of knowledge.
>> And everyone does it. That cat is out of the bag. That lamp has been
>> rubbed. In fact, people couldn't function in any other fashion.

Common.

>>>How are "containment hierarchies" and "organisation hierarchies"
>>>different/the same?

>> It seems that an organizational hierarchy need not be qualified or
>> modified as, organizational. It seems redundant. And if you questioned
>> my phrasing, then it's fair for me to ask that you clarify as well
>> what you meant by "containment".

>Hierarchies denoting part-whole associations.

Actually, the best I could get from a host of senses and definitions of "containment hierarchy" on the web was that it simply is a synonym for the explict/materialized path. But if you mean another sense that a subtree contains proper subcategories, explicit items in a general category, roses under flowering plants and not vis-versa, then obviously there could be hierarchies that are different from that. It's just a matter of definition. If you agree on a definition for the term, you have your answer. Received on Sat Feb 25 2006 - 02:14:52 CET

Original text of this message