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:

 

Find duplicates by catchment:

select * from dedup where catchment = 102030.

Then sort the result by timeuuid (time component only) in-memory and return.