Doing things that makes your database deviate from a normalised model (well, if I remember correctly, there are six steps of normalisation, but the last three may or may not be beneficial - so I'm talking about steps 1-3), should only be done after careful consideration, though I think there may be cases where it is sensible.

I don't quite grasp your structure, so it is difficult to give sound advice. How is COMMUNICATIONS and INCIDENT defined, what is their relationship and approximately how many records are there within each table and for each customer? It could be that an EXISTS query could be a decent choice for you, possibly in addition to some added 'triggerised' field in one of the tables, but I do not know yet.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Maya Opperman
Sent: 4. november 2008 07:49
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Possible to have an index across 2 tables?

Hi All,

I need some advice on how to structure my table for speed, but without
creating a redundant field hopefully.

I need to see the latest communication for a customer. Problem is, the
customer ID is not in the communications table, but is instead linked
via an incidents table.

So, I can't create an index on communications involving the customer ID.
And if I put the customer ID into the communications table, then I run
the risk of a communication belonging to customer A, and also to an
incident which belongs to customer B, due to my database no longer
following a normalised model.

I thought maybe "computed by" was the thing to use, so I looked it up in
Helen's trusty Firebird Book, but I see she warns against doing just
that for this kind of thing (plus the book says the field won't be able
to be indexed anyway)

I'm thinking I'm going to have to create the redundant field and update
it with triggers, but was wondering whether this wasn't a common
problem, with a simple solution that I'm just missing.

Thanks!

Maya