Re: OTLT again

Zsolt Ujvari wrote:
> I searched on the subject and I know/expect to be trashed but I just
have to
> pop it up anyway because I didn't see any discussions on speed
gains/losses
> with a OTLT, only that you loose constraints, data type checks etc.
>
> Today, we have a db structure with 16 lookup tables, all of them with
> "value-description" structure. These are all connected with FK to a
> "document" table, and that means that when you want all the data on
the
> document (like the "values" from the lookups) you have to join 16
tables (+
> some additional tables). So it's pretty big join and is slow. Here
comes the
> first contradiction for me, eg. you should not have more then 4
tables in a
> join, and having lot's of lookup tables instead of OTLT...

You are joking right? Who ever said you shouldn't join more than 4 tables in a query? That's chicken feed to a serious DBMS (and you mention MS and Oracle below, which are serious, unless you meant MS Access?) In any case, with OTLT you will still have the same number of joins, except that many of them will reference the same table.

> In the next version of the app, the users have to be able to create
> unlimited number of lookups - and that's the main reason for posting
here.
> Continuing today's design would mean dinamically creating new tables
(we
> support MS and Oracle but is possible to do it i guess) and joining
to these
> tables for the values. Potensially join on 100 tables??? I guess you
> understand why I think OTLT looks tempting here, simple joins, new
lookups
> are added by inserting some rows in two tables. I would have a new
table
> that connects the "document" to the lookup values table, but I can
live with
> that ...

Well if the requirements really are for users to invent new lookup tables - and lots of them - then probably you do need OTLT. But I can't imagine why that would be the case. Of course users have to create new data, but why new "tables"? Also, if the query requires 100 joins, it will also require 100 joins with OTLT - most of them to the same table.

Regarding performance, have you actually tested your theory that OTLT gives better query performance? I bet it doesn't actually! Received on Sat Nov 13 2004 - 12:48:50 CET