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

[James K. Lowden]

On Tue, 27 Jul 2004 13:23:51 +1000, Zenaan Harkness <address@hidden>
wrote:
> On Tue, 2004-07-27 at 12:59, James K. Lowden wrote:
> 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.

Precisely.  

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

Sure, in principle.  If you could convince me -- I hope you're not going
to try ;-) -- that these attributes were worth maintaining for our problem
domain, then I'd keep a table like that.  That way, every reference to
TitleID is rendered identically when it's looked up.  

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

Yep.  

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

It's an option.  For tiny tables like this, a handful of rows, no RDBMS is
going to have trouble rendering the join for you.  It's a lot less work
for the application programmer to say "where a.TitleID = b.TitleID" than
to cache the lookup table in his own structure and glue it on to the
Persons query results.  

You raised this question in the context of NULLs and optionality.  If I
have 1,000,000 rows in a 15 column table with a 4 column key, and 3 of
those (nonkey) columns are nullable, splitting them off to their own table
(non-null, optional relationship) would require a 4+3 table, and
referencing it would require a 4-column outer join of some 1,000,000 rows.
 Space and time might well dictate using NULL columns instead, using just
one table.  

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

I don't feel like going back to the SQL standards just now, but don't
overlook rules.  The table's definition can include domain restrictions on
a column (or embedded in a user-defined type).  That can buy a lot of
efficiency, partly because the implementation is free to find the cheapest
way to enforce it.  

> > The lowest form of humor, I'm sure you've heard.  ;-)
> 
> Acronym humour about our GUHD work? Oh, oh, you mean the sarcasm...

"The pun is the lowest form of humor.  And the highest form of wit."

(which doesn't speak particularly well of wit, but oh, well.)  

--jkl