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 Subscriber
s 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 Subscriber
s, 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 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.