Hi,

I'm helping support a software package in which we are currently looking
at rewriting.

Within the program are tables with lots of lookup fields. When I say
lots, we could be talking 40 lookup fields for an individual table.

The way it has currently been written (inherited right back from Delphi
1), is that the tables use Lookup Fields that link to other datasets.

In my previous applications, I've always used SQL to link when running
for reports, etc - but the SQL for this will be kind of large with 40 or
more links.

I was wondering, what is the best way to handle this. Should I just go
with a large SQL query with 40 left outer joins, should I keep the
original design of just using many lookup fields with other datasets, or
does anyone have any other suggestions?

Thanks & Regards

Adam

Adam


I don't think there are many other strategies but plenty of tactics. Being a lazy sod I'd go fir whichever is easiest, but I'd also figure in robustness and performance as well. I presume its running over some sort of network using c/s so I'm not sure how much advantage memory tables would give you for the SQL option but it should work well for lookups.

The only alternative strategy I can see is to denormalise the tables a bit and store the lookuped up data in as part of the table or at least have a separate table that's built every so often so that you just have the one join.


Roy Lambert

Adam,

<< I was wondering, what is the best way to handle this. Should I just go
with a large SQL query with 40 left outer joins, should I keep the original
design of just using many lookup fields with other datasets, or does anyone
have any other suggestions? >>

This all really depends upon whether you want this table to be updateable in
your application.  If you use SQL joins, then updateability is out.

Tim Young
Elevate Software
www.elevatesoft.com

Hi Roy,

Thanks for your reply.

Actually, this application is running in local mode. I'd say probably
50/50 between people running it off a server, and people running it locally.

Performance isn't a big issue, as though there are lots of joins, it's
probably one of the smaller database applications I've worked on when it
comes to data per record.

Unfortunately denormalising the table probably isn't the greatest option
in this case, but appreciate the suggestion. In this case, it would
require a lot more fields on the primary table. (ie, one field looking
up names would then transfer to name, address, etc, etc).

Interesting thought though re having a secondary table containing all
the values that can be built every now and then. I'll take that into
consideration. Thanks

Cheers

Adam.

Hi Tim,

> This all really depends upon whether you want this table to be
> updateable in your application.  If you use SQL joins, then
> updateability is out.

Thanks for that. The way I normally design my applications is to have a
separate form / module per report - isolated from the data entry screen.

This allows me to call the form, and pass a primary value and the report
or procedure can be ran independently. In these cases, we are only
retrieving data, not updating data. This also allows these reports or
procedures to be ran behind the scenes, and not interrupt the user on
the current record they may be editing.

Don't know if this is best practise, but it's what I've used todate.
(Although I haven't normally had a table with that many lookups all
needed to be accessed at once before

Where the user edits the data, I use tables with lookup fields in a
separate datamodule.

I suppose I could look at having all the tables setup on an ancestor
form, and inherit that form possibly, and still leave it as tables with
lookup properties instead of SQL?

Cheers

Adam.

Adam,

<< I suppose I could look at having all the tables setup on an ancestor
form, and inherit that form possibly, and still leave it as tables with
lookup properties instead of SQL? >>

As long as you only need to read the data, then the SQL version is fine,
provided that it is performing acceptably for you with that many joins.

Tim Young
Elevate Software
www.elevatesoft.com