OTLT again
Date: Sat, 13 Nov 2004 11:55:32 +0100
Message-ID: <4195e97e$1_at_news.broadpark.no>
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...
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 ...
One lookup looks tempting now, but I'm basically looking for the best sollution for my problem...
Thanks,
Joe
Received on Sat Nov 13 2004 - 11:55:32 CET