Thanks for all the replies!
Our mail server seems to have been sending mail out, but not in, for
most of the day - just to add to the confusion!

This solution below (add CustomerID to table and update via triggers)
might be the one I have to go with (although AKAIK it's poor DB design?
Or is it not considered poor design since it's updated with
triggers...?)

I'm busy having a look at Set's very interesting ideas (time I got to
playing with this new FB stuff!) even though I might not be able to use
it in production for a while yet.

Results not 100% yet though because my FB 2.0 laptop has IBPersonal
edition so I did some tests, but can't see the index usage, and
re-installing FB with ver 2 on my dev PC is now giving me access
violations in my licenced IBExpert (which is a couple of years old) so
now it's off to the boss to get money approved, so I can update, and
then I'll let you know the results from Set's suggestions.


-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of tjelvar eriksson
Sent: 04 November 2008 12:00 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Possible to have an index across 2
tables?

Hi Maya,

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

I figure your databas looks like:

customers -1:n- incidents -1:n- comments,

guess a aggreagated view is as fast as it gets without triggers,
of course with indexed keys, but I recall I had just the same need a
few years ago and the performance was a horror.

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

I'd recommend a "lastCommentDate" or something in the incidents-table,
updated by trigger upon new or altered record in the comments-table.

Good luck,
//t
4 nov 2008 kl. 08.53 skrev Maya Opperman:

>
> Hi Set,
>
> >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?
> Just answered in reply to Helen's mail, but in short:
>
>


> customers -1:n- incidents -1:n- comments
>

> Table Records
> Customers 15000 PK ID
> Incidents 150000 PK ID, CustomerID references Customers.ID
> Comms 1500000 PK ID, IncidentID references Incidents.ID
>
> Need to extract max date in Comms per Customer
>
>

[Non-text portions of this message have been removed]


------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links