just use triggers with care,
(they may shoot at each other...)

It's not bad design. It's "reality over theory". ;-)

(I had a similar problem many years ago like,
users - 1:n - usersmess - n:1 - messages - n:1 - topic,
where i needed max(messagedate) of topic/user.
FB was slow (migrated from ms-sql), solution was
adding topicfk and messagedate (both copies) to usersmess.
)


atb
//t
4 nov 2008 kl. 13.59 skrev Maya Opperman:

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

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