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

[Zenaan Harkness]

On Tue, 2004-07-27 at 12:59, James K. Lowden wrote:
> 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

Hmm ... as a programmer, I tend to think of a lookup as something you'd
load ahead-of-time (or for something like titles that shouldn't change
often, even pre-generate with a script) and then have the application do
it's normal DB query, and then do the lookup in it's own process/
application thread - completely offloading the "join" from the db
itself. But I guess what you want is to have views (therefore joins)
that present just what the application ordered... and I can see how
using this "rule"/domain table thing, kind of gives you the best of both
worlds.

OK, so now the next question (we're going to have the finest design of
names and titles the world has ever seen :)

Internationalization <tada!>
I imagine that in an "ideal" database world, we would have db "lookups"
for things such as (you guessed it) titles, and then a title-lookup
depending on the local language setting. So Mr, Mrs, Ms, etc, would have
different attributes per language:

CREATE TABLE titles
 - language id
 - title id
 - unicode text
 - ascii text
 - gender association (male, female, neutral)
 - can combine with other titles
 - position (left of name, right of name)

In this table, the key is (language id, title id).

Position - where the title normally appears (right/ left of name), etc.

So, does title now become this separate table?

I guess I've just added "other attributes", as you mention above.

> well -- both from an efficiency and a normalization standpoint -- to have

And for sanity ...

> Persons.Title and be done with it.  
> 
> Optional relationships, like joins of any kind, get more expensive as the
> row count grows.  

I really think that "lookup" joins should be in an external process.
This could still be hidden from the application, but it should happen in
application space - so the application's db driver would do such joining
of lookups at its own end, not at the database end. Doesn't this make
sense?

Perhaps it's a question to ask the Postgres guys, since they might be
interested in this sort of performance tweaking. I guess you'd have to
define lookup tables with an attribute specifying that it's a lookup, or
perhaps the system can do a client-side join if the joinee is small
enough or something.

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

Acronym humour about our GUHD work? Oh, oh, you mean the sarcasm...

<fail's to otherwise come up with witty response - oh well, enough for
today>

cheers
zen