Re: [hfdb] Scope (Was Re: Grand Unified Hardware Database)

[James K. Lowden]

On Tue, 27 Jul 2004 07:22:46 +1000, Zenaan Harkness <address@hidden>
wrote:
> > Relational theory says you represent optional relationships in
> > separate tables and disallow nulls.  That makes for a lot of joins
> > that, in practice, often performs poorly.  Hence nulls.  
> 
> Ahah! Simply poor performance.
> 
> This kind of relates to my earlier question re: lookup tables - and eg.
> lookup for "title" (mr, mrs, ms, dr, sri swami :). Such a lookup table
> is not an _optional_ relationship, since it's not a "separate relation"
> table, it's just a lookup from the original table to the sub table
> (sorry if I'm not using proper terms here).
> 
> Am I correct in assuming that joins to lookup tables do not have the
> performance problems that joins with optional data does?

Well, you know, the RDBMS doesn't really care whether you call your table
a lookup table or a domain table or an optional relation or a poor
relation or a crazy aunt in the attic.  It just joins stuff.  ;-)  

If you had a table:
        create table Titles 
                ( TitleID int
                , Title char(10)
                )
and stored TitleID in the Persons table, forcing a join to Titles for
every address label, you'd have a (small) performance impact irrespective
of the optionality of the relationship.  Better would be a domain table
(or rule, I think that's standard SQL nowadays):

        create table Titles 
                ( Title char(10)
                )
and have every Person insertion/update have its Title field validated
against Titles.  As long as Titles has no other attributes, it's just as
well -- both from an efficiency and a normalization standpoint -- to have
Persons.Title and be done with it.  

Optional relationships, like joins of any kind, get more expensive as the
row count grows.  

> Well, this is world domination we are out to achieve here! We hope to
> become the Grand Unified Hardware Database (pretty guhd stuff huh? :)

The lowest form of humor, I'm sure you've heard.  ;-)

> >  In fact, I'd be surprised if that hasn't been done already.
> > Once we're that far, we'd need some XSLT expertise to transform
> > the "standard" XML into the desired form.  
> 
> Like HTML? Or just a transform of the XML itself?

The idea being that our "standard" XML, derived from the schema, might be
transformed with XSLT into someone else's standard form.  Sure, HTML is
one option.  

> Nothing like consensus on an issue.

Aye, aye, Captain.  

--jkl