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

>How do you figure this? What I would expect is the following:

>CUSTOMERS, PK CustomerID

Correct

>INCIDENTS, PK IncidentID

Correct, but add FK Customer references Customers (CustomerID)

>COMMUNICATIONS, PK CommunicationID, FK Customer references Customers

(CustomerID), FK Incident references Incidents (IncidentID)
Contains no customer ID

>This is how I think it ought to be, given the following assumptions:
>(a) one incident might involve multiple customers.

No only one incident per customer. Can think of an incident as being a
bit like a 'support ticket'. We do also have another table called tasks
(which links to bugs fixed, new features added, enhancements made, etc)
which would fit your description better.

>(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
As above - this would describe our tasks (bug tracking) table, incidents
belong to one customer only.

We can't drop the incidents table, as there is not always a task for
every incident (although I do my best to create one, as that means less
support queries in future!). The incident may also revolve around a
sale, and we need the unique reference number to know which
"conversation" to continue updating when we get a response.

Any more ideas?

Thanks
Maya