1

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):

enter image description here

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 #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;
  • So, after Edit # 2 your problem is solved. Right? – Walter Mitty Feb 7 '18 at 14:53
0

I believe you are wanting something a little like this;

SELECT DISTINCT
Subscriber.id
FROM Subscriber
 INNER JOIN ShippingAddress ON (Subscriber.id = 
ShippingAddress.subscriber_id)
  LEFT OUTER JOIN NonDeliveryReport ON (ShippingAddress.id = NonDeliveryReport.shipping_address_id)
WHERE NonDeliveryReport.shipping_address_id IS NULL OR NonDeliveryReport.resolved IS NOT NULL;
0

You didn't supply any test data in your question, so this is not fully tested. You also didn't specify a RDBMS platform (SQL Server, Oracle, etc.). I used SQL Server and a couple of Common Table Expressions.

Your ineligible is any subscriber where there was a non-deliverable address AND any subscriber where the last_payment is over 60 days ago. I used a UNION (which will eliminate any duplicates). The eligible subscribers are anyone who is NOT ineligible.

;

WITH SubscriberIneligible
AS (
    SELECT Subscriber.id        --non-deliverable
    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

    UNION

    SELECT Subscriber.id        --last payment over 60 days
    FROM Subscriber
    WHERE Last_Payment < DATEADD(DAY, - 60, SYSDATETIME())
    )
    ,SubscriberEligible
AS (
    SELECT Subscriber.id        --eligible subscribers
    FROM Subscriber s
    WHERE NOT EXISTS (
            SELECT *
            FROM SubscriberIneligible
            WHERE id = s.id
            )
    )
SELECT *
    ,'N' AS Eligible
FROM SubscriberIneligible

UNION

SELECT *
    ,'Y' AS Eligible
FROM SubscriberEligible
0

IMHO ,your database design is OK.Hope data type are also ok.

Shipping table is not much clear.Explain the column purpose

Don't say ,"But query 1 kills me".say the exact problem.how much time it takes.whether it giving correct output or not etc.

BTW with your existing design,you can avoid distinct in this manner

SELECT 
  Subscriber.id
FROM dbo.Subscriber
INNER JOIN dbo.ShippingAddress ON (Subscriber.id = ShippingAddress.subscriber_id)
where exists(
select shipping_address_id from NonDeliveryReport NDR
WHERE ShippingAddress.subscriber_id= NDR.shipping_address_id AND
 NonDeliveryReport.resolved IS NULL
)

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.

your thinking is correct.you should add isDeliverable (bool) in Subscriber table.It do not defeat the purpose of Normalization. Firstly we should create Normalize database as far as possible.Now according to requirement and data volume and frequency we have liberty of DeNormalization. It is very much recommended by RDMS book.

Performance is Main Goal

In case query performance is hurting then you can add few column in order to denormalize and hence reduce JOIN condition.

you can even introduce shipping_id (fk) , subscriber_id (fk) in NonDeliveryReport table but it very much depend upon your your MIS requirement,volume of data

0

This is the right solution.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.