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 subscribers_number, first_name, last_name and 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 subscribers_number.

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

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:

1. Select all subscribes that are eligible.
2. Select all subscribers that are not eligible.

Query two 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 query 1 kills me. 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.