Re: assigning codes to events

I can see at least the following Problems with both designs

        Not 1NF
        Can't hold letters in progress
        Hasn't got Delivery Date
        Hasn't got Returned Date
        A Letter can't be Returned because of No Address AND No Postage

This would be my first stab

Letters (L)

LetterID DateSent
--------- ----------

1         5-15-2002
2         5-16-2002
3         5-17-2002
4         5-17-2002
5         5-18-2002

Letters_Delivered (D)

LetterID DateDelivered
--------- ----------

1         5-18-2002
2         5-17-2002

Letters_Lost_View

SELECT LetterID FROM Letters
WHERE Current Date - DateSent > 15 Days EXCEPT SELECT LetterID FROM Letters_Delivered EXCEPT SELECT LetterID FROM Letters_ReturnedToSender

Letters_ReturnedToSender (R)

LetterID DateReturned Sufficent_Postage Address_Known
--------- ------------ ----------------- ---------------

4         5-17-2002    0                 1
5         5-18-2002    1                 0

(I note the booleans above might not be sufficient. E.g. 0 = False, 1 = True, 2 = Not Known)

Add then you need some constraints (of course)

NOT EXISTS (SELECT LetterID FROM R INTERSECT SELECT LetterID FROM D)
NOT EXISTS (SELECT LetterID FROM R EXCEPT SELECT LetterID FROM L)
NOT EXISTS (SELECT LetterID FROM D EXCEPT SELECT LetterID FROM L)
NOT EXISTS (SELECT LetterID FROM D NATURAL JOIN L
              WHERE DateDelivered < DateSent FROM D, L)
NOT EXISTS (SELECT LetterID FROM R NATURAL JOIN L
              WHERE DateReturned < DateSent )
NOT EXISTS (SELECT LetterID FROM  R 
              WHERE Sufficent_Postage = 1 AND Address_Known = 1 )

Now that's a little better

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Wed Jun 19 2002 - 12:22:45 CEST