Dedup

DB design: 

CREATE TABLE dedup (
catchment_id varchar,
health_id1 varchar,
health_id2 varchar,
rule_name varchar,
rule_value varchar,
created_at timeuuid,
PRIMARY KEY (catchment_id, health_id1, health_id2, rule_name, rule_value));

Example:

Feed - p1 (create), p2 (create), p3 (create), p1 (updated occupation), p1 (updated nid2), p2 (voided, merged-with p3), h4 (create), p5 (create). pn represents HID.
All have same NID, nid1 but different catchments, c1, c2, c3, c4, c5. Only, h4, h5 have same phone #. c1 - 101112, c2 - 202122, c3 - 303133, so on.
Rules: same nid and same phone # are dedups. Dedups cannot be resolved for just one rule, but for all.

Scenario 2 (DB Calls): 

#1 select * from nid_mapping where nid = nid1

#2 select catchment from patient where hid in (h1, h2)

#3. Dedup DB:

Searches all patients by phone # ph2. No matching patient. No action taken.

Scenario 3: 

#1 select * from nid_mapping where nid = nid1

#2 select catchment from patient where hid in (h1, h2, h3)

#3. Dedup DB:

Searches all patients by phone # ph3. No matching patient. No action taken.

Scenario 4: 

#1 select hid2 from dedup where catchment in (1011, 101112) and hid1 = h1. 

#2 select catchment from patient where hid = .. (for each hid returned in #1)

#3

(Batch begin)

delete from dedup where catchment_id in (1011, 101112) and hid1=h1

delete from dedup where catchment_id in (...) and hid1=.. and hid2=h1 (repeated as many times required)

repeat scenario 2.

(Batch ends)

#4 Dedup db looks same as in scenario 3.

Note: If catchment is updated, it will be similar to scenario 3.

Scenario 5: 

Similar to scenario 4. 

Dedup db:

Scenario 6:

Similar to scenario 4. Dedup table becomes empty.

Scenario 8:

Similar to scenario 2. 

Dedup table:

Scenario 9:

Similar to scenario 2. 

Dedup table:

Scenario 10:

p3 and p4 are no more dupes. 

#1. select catchment from patient where hid in (h3, h4)

#2. delete from dedup where catchment in (..) and hid1 in (h3, h4) and hid2 in (h3, h4) [in won't work. so run separate queries in batch]

Dedup table: