Twelve Million Subscriptions Behind an Extended Recipient Schema
A retail client keeps newsletter preferences on an extended nms:recipient schema: twelve boolean fields plus one text field holding a comma-separated list of categories. Marketing now wants every subscription tracked separately with its own opt-in date, acquisition source and double opt-in status, and Legal wants proof of when each contact consented and when they withdrew. Volumes: 8 million recipients, about 12 million subscriptions, growing at 400k per month. The nightly segmentation workflow already runs 70 minutes, and the recipient table carries 60 columns. Developer A proposes twelve more fields plus a JSON blob holding the history. Developer B proposes a new schema, cus:subscription, linked 1:N to the recipient. The DBA objects that any new index on the recipient table slows the nightly CRM synchronisation, which already overruns its window.
1. Choose between the two designs. Justify the decision on cardinality, indexing and impact on the CRM synchronisation, and state what you would verify on the link itself.
Read the worked answer
Choose design B. A consent history is a child table by nature: roughly 1.5 rows per parent, growing by one row per event rather than one column per event.
Why A fails. Twelve booleans cannot carry an opt-in date or a withdrawal date. A JSON/TEXT blob cannot be indexed, so every query activity would scan nms:recipient - the hottest table in the instance. Each added column also widens the table, and every added index is maintained on each insert and update coming from the CRM sync, which is exactly where the DBA is losing time.
What B must get right.
- cus:subscription holds the FK to recipient, category, optInDate, optOutDate, source, doubleOptIn flag.
- Confirm the FK column is actually indexed; if the link was declared without a matching
<dbindex>, add it and run the database update wizard. - Add a composite index that matches the real filter, e.g. (iRecipientId, status) for point lookups or (status, optInDate) for date-range scans.
- Check the link integrity attribute: with
integrity="own"deleting a recipient cascades to the child rows and destroys the evidence. If Legal requires retention, use a neutral link and anonymise through a dedicated workflow.
The CRM sync is untouched because nothing is added to nms:recipient.
2. The nightly workflow must output 'recipients with at least one active subscription in the last 30 days' in under 15 minutes. Describe the query shape, the index you would rely on, and one alternative if the join stays too slow.
Read the worked answer
Filter on the child side first, then join up to the recipient - never the reverse, because the selective predicate lives in cus:subscription.
Query shape. A Query activity on cus:subscription with the date and status condition, then a link to the recipient, then a Deduplication activity on the recipient internal id ('keep first'). Deduplicating on the delivered internal id is cheap; a DISTINCT over 12M wide rows is not.
Index. The filter is a table-wide range scan, so the leading column must be the selective one: (status, optInDate, iRecipientId) if you also need the FK covered. An index led by iRecipientId only helps point lookups, which is not this pattern.
If it stays slow. Move the cost off the critical path: a daily incremental workflow that recomputes, per recipient touched, a lastActiveSubDate field on an extended recipient schema, then the loyalty query becomes an indexed range scan on that field. This is a deliberate denormalisation: accept write cost on changed rows in exchange for a predictable read.
Also check that the deduplication runs after any 1:N join, otherwise it silently does nothing and you pay for both the fan-out and the sort.
3. Legal asks what happens to consent evidence when a contact exercises their right to erasure. Outline the retention design and where it conflicts with the link integrity you chose.
Read the worked answer
This is a conflict to be resolved explicitly, not a technical default.
If the link is own: erasure of the recipient cascades to cus:subscription, so the evidence disappears. Compliance usually accepts this - you no longer need consent for data you no longer hold - but the deletion must itself be logged somewhere outside the recipient schema, for example an audit table keyed by a pseudonym.
If the link is neutral: the parent can be deleted while child rows survive, which preserves the proof but leaves orphaned personal data unless you anonymise it. Then the erasure workflow must blank the identifying fields on the child rows and keep only category, dates and a pseudonymous key.
What to decide with Legal. What must be provable after erasure: the fact of consent, the date, the source - not the identity. That points to pseudonymised retention.
Operational consequences. The purge/erasure workflow becomes a delivery-adjacent process that must be tested against the volume: 12M rows means batching the deletions, and every cascading delete on a parent with many children must be indexed by FK or it degrades into a scan.