I am designing a database for a publishing house to track magazine shipping and also to keep a history of it.
I have three tables (simplified):
Subscriber id (pk) subscribers_number (unique) first_name last_name shipping_address last_payment Shipping id (pk) date issue ShippingAddress id (pk) shipping_id (fk) subscriber_id (fk) subscribers_number first_name last_name shipping_address NonDeliveryReport id (pk) shipping_address_id (fk) reason resolved
Subscriber is a list of people who pay to receive a monthly issue of their magazine. Not every subscriber is eligible to receive the next issue. To make it simple, a subscriber receives the next issue only if
last_payment date is withing the last 60 days. There are some more conditions, but I'll get into that later.
When the next issue is ready to be shipped, a
Shipping is created.
Then we select all eligible
Subscribers and create a
ShippingAddress for each one and copy the value of
shipping_address from the
Subscriber to the new
ShippingAddress. We do this so that we have an unmutable history of shipments, even if a
Subscriber changes his/her address, or is deleted from the database. For ease of use, there is a nullable foreign key
subscriber_id in addition to the hard copy of the
If the mail company could not deliver to a subsriber's
ShippingAddress, they return a non-delivery report to us. For each report, a
NonDeliveryReport is created and references the affected
Now we are getting to the important part of my question:
When we create a Shipping, we have to select all
Subscribers, that are eligible. Eligible is every subscriber, except for anyone, who has a non-delivery report attached to him, that was NOT
resolved. (For simplicity's sake, we just ignore all other conditions.)
So I need two queries:
- Select all subscribes that are eligible.
- Select all subscribers that are not eligible.
Query #2 was managable and rather easy:
SELECT DISTINCT Subscriber.id FROM Subscriber INNER JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.subscriber_id) INNER JOIN NonDeliveryReport ON (ShippingAddress.id = NonDeliveryReport.shipping_address_id) WHERE NonDeliveryReport.resolved IS NULL;
But the queries I wrote for case #1 always returned fewer or more results than expected. I am stuck on this since last week and just can't get it to work. I hope you could help me with that.
This isn't part of my main question, but any thoughs (in form of comments pls) about my database design are welcome. I was already thinking about adding a field
deliverable (bool) to
Subscriber and just fill in the value programmatically, when importing the NDRs, but am hesitant because that'd probably defeat the purpose of database normalization.
Edit #1: The closes I came to was the following query, but it returns too many rows. *sigh*
SELECT DISTINCT Subscriber.id FROM Subscriber LEFT JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.Subscriber_id) LEFT JOIN NonDeliveryReport ON (ShippingAddress.id = NonDeliveryReport.ShippingAddress_id) WHERE NonDeliveryReport.korrigiert_am IS NOT NULL OR NonDeliveryReport.id IS NULL OR ShippingAddress.id IS NULL
Edit #2: After thinking even harder (I didn't think that was possible any more) I found a solution.
When non-eligible are subscribers that have an open NDR (resolved IS NULL), then eligible is anyone who as no NDR in addition to anyone who has more than 0 unresolved NDR.
-- List of ELIGIBLE Subscribers SELECT DISTINCT Subscriber.id FROM Subscriber LEFT JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.subscriber_id) WHERE ShippingAddress.id IS NULL OR Subscriber.id NOT IN ( SELECT ShippingAddress.subscriber_id FROM ShippingAddress INNER JOIN NonDeliveryReport ON ShippingAddress.id = NonDeliveryReport.shipping_address_id WHERE (NonDeliveryReport.id IS NOT NULL AND NonDeliveryReport.resolved IS NULL) GROUP BY ShippingAddress.subscriber_id ) ORDER BY Subscriber.id;