| Subject | Re: [firebird-support] Possible to have an index across 2 tables? |
|---|---|
| Author | Helen Borrie |
| Post date | 2008-11-04T07:30:01Z |
At 05:48 PM 4/11/2008, you wrote:
CUSTOMERS, PK CustomerID
INCIDENTS, PK IncidentID
COMMUNICATIONS, PK CommunicationID, FK Customer references Customers (CustomerID), FK Incident references Incidents (IncidentID)
This is how I think it ought to be, given the following assumptions:
(a) one incident might involve multiple customers.
(b) you don't know which customers are affected by the incident until you get a communication from them...and you don't necessarily know an incident has occurred until you hear from the first customer
So your primitives here are CUSTOMERS (which exists, regardless of incidents or communications) and INCIDENTS (which may affect many customers).
The *intersection* of these two primitives is COMMUNICATIONS - the classic resolution of a many:many relationship. Make sure your application creates the INCIDENT record before it tries to create the first COMMUNICATIONS record pertaining to the incident. The paired FKs (joined at the hip by a UNIQUE constraint) give you access to:
1) All communications relating to a single incident
2) All customers who have communicated about any incident
3) The communications about an individual incident from a single customer
and even
4) Customers who *didn't* communicate about a particular incident (if there's no communication record for customer X and incident Y, then that customer wasn't affected seriously enough by the incident to complain!)
./heLen
>Hi All,The answer to the question in the subject is a resounding NO. It would win you nothing, anyway. Indexes help for two things: finding a value in a column or helping with ORDER BY and GROUP BY operations.
>I need some advice on how to structure my table for speed, but withoutYou *get rid of* redundant fields, not *create* them.
>creating a redundant field hopefully.
>I need to see the latest communication for a customer. Problem is, theHow do you figure this? What I would expect is the following:
>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.
CUSTOMERS, PK CustomerID
INCIDENTS, PK IncidentID
COMMUNICATIONS, PK CommunicationID, FK Customer references Customers (CustomerID), FK Incident references Incidents (IncidentID)
This is how I think it ought to be, given the following assumptions:
(a) one incident might involve multiple customers.
(b) you don't know which customers are affected by the incident until you get a communication from them...and you don't necessarily know an incident has occurred until you hear from the first customer
So your primitives here are CUSTOMERS (which exists, regardless of incidents or communications) and INCIDENTS (which may affect many customers).
The *intersection* of these two primitives is COMMUNICATIONS - the classic resolution of a many:many relationship. Make sure your application creates the INCIDENT record before it tries to create the first COMMUNICATIONS record pertaining to the incident. The paired FKs (joined at the hip by a UNIQUE constraint) give you access to:
1) All communications relating to a single incident
2) All customers who have communicated about any incident
3) The communications about an individual incident from a single customer
and even
4) Customers who *didn't* communicate about a particular incident (if there's no communication record for customer X and incident Y, then that customer wasn't affected seriously enough by the incident to complain!)
./heLen