Re: Something new for the New Year (2008).

From: Rob <>
Date: Thu, 17 Jan 2008 10:34:12 -0800 (PST)
Message-ID: <>

On Jan 15, 6:03 pm, JOG <> wrote:
> On Jan 12, 8:01 pm, Rob <> wrote:
> > On Jan 10, 11:14 am, Gene Wirchenko <> wrote:
> > >Rob <> wrote:
> > >>1. Statement of Facts, Example 1:
> > >>2 sets if propositions (i.e., 2 tables: Adult, Child)
> > >>------------
> > >>Al is a male adult, age 45 => (adultname:A,sex:male,age:45)
> > >>Bob is a male child, age 15 => (childname:Bob,sex:male,age:15)
> > >>Carol is a female child, age 13 => (childname:Carol,sex:female,age:
> > >>13)
> > >>Dee is a female child, age 8 => (childname:Dee,sex:female,age:8)
> > >>what I referred to as JT's:
> > >>----
> > >>Al is father of Bob => (father:Al,child:Bob)
> > >>Al is father of Carol => (father:Al,child:Carol)
> > >>Al is father of Dee => (father:Al,child:Dee)
> > >>2. Now, although Al is father of Bob, Carol and Dee, only Bob and
> > >> Carol are siblings. Dee is a child of a 2nd marriage. We want
> > >> to capture the fact that Bob and Carol are siblings, but Bob
> > >> and Dee (and Carol and Dee) are half-siblings. So, instead of
> > >> the JT (i don't know what you call it), I propose the following
> > >> Aggregate and Link:
> > >>Aggregate:
> > >>--
> > >>Al is the father in family unit 1 => (familyunit:1,familyfather:Al)
> > >>Al is the father in family unit 2 => (familyunit:2,familyfather:Al)
> > >Not workable. They can all be the same family unit. What you
> > >are missing is maternity. Add Susan and Vicki who are Al's first and
> > >second wives and, more importantly, the mothers. You can then express
> > >that Bob and Carol are siblings (because they have the same father and
> > >the same mother) and that Carol and Dee are half-siblings (because
> > >they only have one parent in common).
> > >Since motherhood has been brought into the picture, maybe add a
> > >predicate for "likes apple pie". <BEG with ice cream>
> > >Sincerely,
> > >Gene Wirchenko
> > I don't know what you mean by "unworkable". Does that mean it is not
> > well-formed, or just that you have a different model?
> > According to Codd, a data model must have generalized integrity rules.
> > Tell me if my "aggregates" and "links" above violate the rules of your
> > data model.
> > Here's another stab that introduces mothers (though I know nothing
> > more than their names, hence no propositions about these mothers):
> > Propositions (2 sets, 2 relations, Adult and Child)
> > ------------
> > Al is a male adult, age 45 => (adultname:Al,sex:male,age:45)
> > Bob is a male child, age 15 => (childname:Bob,sex:male,age:15)
> > Carol is a female child, age 13 => (childname:Carol,sex:female,age:
> > 13)
> > Dee is a female child, age 8 => (childname:Dee,sex:female,age:8)
> Fine, simple propositions.
> > Aggregate:
> > ---------
> > In one marriage, Susan is the mother, Al is the father =>
> > (mother:Susan,father:Al)
> > In another marriage, Vicki is the mother, Al is the father =>
> > (mother:Vicki,father:Al)
> Aggregates? These are just propositions with two foreign keys - surely
> this is just an associative entity/junction table/intersection table.
> > Link:
> > ----
> > Bob's mother is Susan => (child:Bob,mother:Susan)
> > Carol's mother is Susan => (child:Carol,mother:Susan)
> > Dee's mother is Vicki => (child:Dee,mother:Vicki)
> Links? These are just propositions with a single foreign key (PKFK I
> think you called them). I don't understand why you have renamed these
> things...
> > I'm not arguing that this is the best database design.
> > JOG said: "Your AL structure corresponds to no statement of
> > fact that I can think of." I'm trying to address his claim.
> > Rob
> Honest opinions here Rob. First, while there are issues with this
> example of course, it is still far better than your web page imo,
> which was very convoluted (and set my crank alarm bells ringing I'm
> afraid - best to know that though right?)
I've always assumed you were expressing your honest opinions.
> Second, I certainly don't think what you have observed is
> revolutionary and claiming it as "first new relationship to be
> discovered for 33 years" comes across as a bit silly to my ears. /
> However/, that is not to say there is not value in there somewhere, so
> please don't think I'm just doing you down...
If you are going to quote me, please be accurate. I have never used the word "revolutionary". That adjective can only be applied retrospectively (like "the RM was revolutionary"). Also, I only claim it is the "first, new relationship REPRESENTATION to be discovered in 33 years," not the "first new relationship". There is definitely utility. Whether it can be translated into value is as yet unknown. As I said in fasttrack, "With the passage of more than 30 years, conventional PKFK and JT representations may constitute a legacy that renders A-L superfluous."
> I think you have in fact identified a common database design pattern,
> and awareness of it (though I dislike the nomenclature that you have
> conjured) is a very useful thing for database designers considering
> their schemas. And as such, I think you should continue to investigate
> database schemas, identifying other design patterns, and then collate
> them all together - that could be useful stuff and ultimately get you
> a lot more credit for it than any hand wavy stuff.
> If you don't agree fair enough, but that is my honest analysis. All
> best, Jim.
For 2 weeks, you and I have had a dialog with each of us operating at a different level of abstraction within the universe of relational data models. As Codd says in his 1980 paper, "DATA MODELS in DATABASE MANAGEMENT": <quote>
Many people fail to separate in their minds different levels of abstraction. A specific example of this is the failure to realize that tuples are at a higher level of abstraction than records (one is not allowed to use the contiguity of components of tuyples, whereas one can use the contiguity of fields in a record). </quote>

So when (for example) CELKO says (#239 in "Newbie question about db normalization theory: redundant keys OK?"): 'I need to add that [''Links point, foreign keys constrain''] to my "columns are not field, rows are not records, files are not tables!" rant', he is demonstrating his intolerance to the lower level abstractions (fields, records, files) that programmers must face as they cross the seam between a data model implemented in an SQL DBMS and the external representation of a query response. (Which you must admit looks alot like a file of ordered, fixed-field records.)

My whole purpose was getting past your claim: "Your AL structure corresponds to no statement of fact that I can think of." I worked up my example so we could have one simple point of agreement from which we could proceed: That there are "statements of fact" (propositions) that map to the A-L data structure. Your data model (RM) must be mapped to an RDM engine to actually define and use relational databases. Your data model /seems/ to employ 2 "design patterns" (I also dislike that term) that conveniently map to the two data structures that I call "PKFK" and "JT". All I did in my example (above) was to show how propositions can also map to A-L. And you are absolutely correct, A-L is made up of a pair of these data structures, one PKFK and one JT. (Everything "new" is made up of components that are "old" -- just check the patent literature.)

I work at different abstraction levels than you. My main concern has been relational systems development, so naturally I have also had to pay attention to the SQL paradigm level as defined by ANSI SQL standards. You have disparagingly used the terms "hocus pocus" and "hand wavy stuff" to refer to the fasttrack contents of my website, but I suspect your biggest objections are to my use of NULLs, relationships and the terms "parent" and "child". I can't banish NULLs just because practioners at your level of abstraction don't use them, and I can't escape relationships, parent- and child tuples because that's what has to be implemented. (If you ignore NULLs at the SQL level, you will see that instead of 4 relationship types for PKFK, 4 for JT and 20 for A-L, you actually end up with 2, 2 and 10. What a surprise.)

My biggest gripe about SQL is not data models, but how it tolerates denormalized multisets as query responses. Why do we work so hard to get a normalized database and then put up with query responses which destroy that normalization? The SQL standard could easily admit responses to SPJ queries that are recursive lists. A recursive list can be denormalized to produce a report if needed, but a recursive list is a far superior form for data delivery to an application than a denormalized multiset.

And finally, I have investigated all the design patterns that employ 3 or fewer foreign keys. There's only one that hasn't been mentioned, and it is of interest for transaction processing environments, not distributed OLAP. I had an excellent research mentor and I think the website reflects a fairly thorough examination of all important aspects of the new, A-L database data structure. Once upon a time, I worried about getting the kind of "credit" you speak of (above) for my research results. Now I'm more interested in 1) making database technology more widely available, 2) analytics, 3) using lattice relationships to solve problems not previously considered solvable within the SQL paradigm and 4) integrating databases across networks

Let's move on. This thread has been exhausted. I started a new thread "Data Models and Levels of Abstraction". Let's continue the conversation there.

Rob Received on Thu Jan 17 2008 - 19:34:12 CET

Original text of this message