Subscription Address Maintenance

For a variety of reasons, we may have subscriptions (and related invoices) in the system that point at non-existing addresses. We are trying to fix the problem on the root-cause-side as well, but since this is still sometimes happening for various reasons, we run a process that tries to find and fix such subscriptions and invoices.

Subscriptions with Invalid Shipping Address

Subscriptions with invalid shipping addresses are subscriptions that point to non-existing addresses. Note that we also have subscriptions that simply do not point to a delivery address (or, in technical terms, they point to Guid.Empty). This happens for a variety of reasons, one of them being that if we do not have a way to fix up the address assignment, we may choose this as the least evil choice (see below).

The overall process follows this logic:

  1. The system queries a list of all subscriptions that have a foreign key for the shipping address that is not found in the Address table (and is pointing at a non-empty address Guid).
  2. The system then loads the Person/Name associated with the subscription to see what addresses are associated with that person.
  3. The system then tries to pick one of those addresses based on the following rules:
    1. If the person has no addresses, and the subscription is a print subscription, the subscription is turned into a digital subscription and the address is set to Guid.Empty
    2. If the person has exactly one address, we assign that address as the subscription delivery address.
    3. If the person has more than one address, and all those addresses are the same, we assign the first address.
    4. If the person has more than one address, and all but one addresses have only empty fields (the root cause of which is a mystery), we pick the non-empty address.
    5. If the person has more than one address, and at least one of them is a verified US address, we use that address.
    6. If the person has more than one address, and at least one of them is a US address, we use that address.
    7. If the person has more than one address, we use the first non-empty address, regardless of where it is from.

Subscriptions with Invalid Billing Address

This is quite similar to the shipping address problem above, however, it only applies to paid subscriptions.

The overall process follows this logic:

  1. The system queries a list of all subscriptions that have a foreign key for the billing address that is not found in the Address table (and is pointing at a non-empty address Guid).
  2. The system then loads the Person/Name associated with the subscription to see what addresses are associated with that person.
  3. The system then tries to pick one of those addresses based on the following rules:
    1. If the person has no addresses, the billing address is set to Guid.Empty
    2. If the person has exactly one address, we assign that address as the subscription delivery address.
    3. If the person has more than one address, and all those addresses are the same, we assign the first address.
    4. If the person has more than one address, and all but one addresses have only empty fields (the root cause of which is a mystery), we pick the non-empty address.
    5. If the person has more than one address, and at least one of them is a verified US address, we use that address.
    6. If the person has more than one address, and at least one of them is a US address, we use that address.
    7. If the person has more than one address, we use the first non-empty address, regardless of where it is from.

Invoices with Invalid Address

These are invoices that point to addresses that do not exist in the address table.

The overall process follows this logic:

  1. The system pulls all invoices that have an address ID that isn't found in the Address table (and the ID is not Guid.Empty)
  2. The system then tries to pick an address based on the following rules:
    1. If the person has no addresses, the billing address is set to Guid.Empty. Note that this really is not good, as an invoice without an address is not really valid. But at least we are not pointing an non-existing data.
    2. If the person has exactly one address, we assign that address as the invoice address.
    3. If the person has more than one address, and all those addresses are the same, we assign the first address.
    4. If the person has more than one address, and all but one addresses have only empty fields (the root cause of which is a mystery), we pick the non-empty address.
    5. If the person has more than one address, and at least one of them is a verified US address, we use that address.
    6. If the person has more than one address, and at least one of them is a US address, we use that address.
    7. If the person has more than one address, we use the first non-empty address, regardless of where it is from.

Semi-Orphaned Address records

Addresses are usually linked to people through the Placement table. However, sometimes addresses get created that are stored in the Address table and linked to other entities, such as Subscriptions or Invoices, but not to people. This is not a serious problems, as these records aren't really orphaned (We call these “semi-orphaned”). However, it isn't following the overall design of the system, which calls for address records to be associated with people. As a result, some processes (such as creating distribution lists, or editing subscribers) may not work reliable or produce unintended consequences. Therefore, these records should be re-linked and checked for duplication. Our process does this in the following fashion:

  1. The system queries SQL Server structure information to find all tables that appear to have fields that are linked to addresses. (Field names matching the pattern `fk_%address%' are considered.) This means that if new tables are added to the system that are linked to addresses, this algorithm will attempt to autoamtically pick them up.
    1. The Placement table is specifically excluded from this, since it is a special case, in this sense.
    2. The AdInsertion table is excluded, because it doesn't provide all the information required to follow the next steps.
  2. The system now automatically queries all these tables and looks for records that point at addresses that are not linked to inthe Placement table, and thus are not associated with names.
    1. These tables are also linked to the Names table in some way (usually through an fk_names field) and we can thus figure out what name the addresses shoudl really be linked with.
  3. The system checks whether the name associated with the entity in question already has an identical address to the semi-orphaned address.
    1. If an identical address is found, the system goes through all the tables that use address information (using the same generic algorithm to find such tables) and replaces all references to the semi-orphaned address with the address associated with the name. The system then performs another check to see if the semi-orphaned address is linked somewhere (which could happen if an update failed). If the address is not in use elsewhere (it almost never should be in use at this point in the process), then the address that is now truly orphaned (and not needed anymore) will be removed from the system.
    2. If no address was found, the semi-orphaned address is linked to the name in the Placement table. The address is thus not semi-orphaned anymore and can just be used as a regular address in our system.

Orphaned Pointers in Palcement table

For various reasons, it can happen that the Placement table (the table that links Names to Addresses) points to addresses that to not exist. There is no reason to keep those “pointers into nothing” around, so those records are removed.

Duplicate Placement records

In some scenarios, we end up with duplicate records, linking the same name to the same address (with the same type) through the Placement table. We remove those, since they are literally just duplicates that cause problems.

Previous
Next