CODEWITHSUNDEEP
sqlpython-3.xpostgresql
Shubham Srivastava
Shubham Srivastava

Reputation: 1293

Business logic for identify SQL column update

I have a SQL table called contacts which as n number of rows where n is more than 10Lakh (1 million) rows.

Below is the table structure with dummy data

+---------------+------------+---------+-----------+---------+------------------------+
|     email     | department |  title  |   state   | country | cleansing_verification |
+---------------+------------+---------+-----------+---------+------------------------+
| [email protected] | h.r.       | sr. Exe | telangana | Ind     |                      - |
+---------------+------------+---------+-----------+---------+------------------------+

So, I have 4 schedulers to cleanse the data which is present in the above table, ie

  1. department cleanser
  2. title cleanser
  3. state cleanser
  4. country cleanser

Each cleanser will update the data of the respective columns. I have added one more column call cleansing_verification to identify which column is updated but not able to use properly.

One email can be touched by any of the cleansers. Which means all 4 can update the value or any 3 or any 2 or only 1.

So, the problem is I'm facing is How to identify which email is touched and which is not so that for remaining I can send an email notification.

If something more need let me know I will add in the question.

Thanks in advance.

Upvotes: 0

Views: 36

Answers (1)

MarcinJ
MarcinJ

Reputation: 3639

So normally we don't do this in the world of database design, but you could use bitfields. So your cleansing_verification is a BIT(4) type column, and each cleanser gets a bit they can set:

  • department = B'1000'
  • title = B'0100'
  • state = B'0010'
  • country = B'0001'

When running i.e. state, you would then:

UPDATE contacts 
   SET cleansing_verification = cleansing_verification | B'0010'
 WHERE -- whatever conditions you want to apply

If you wanted to check which rows were updated by a given cleanser, you check if the bit is set, e.g. for state:

SELECT * FROM contacts WHERE cleansing_verification & B'0010' = B'0010'

Working example on dbfiddle

Actually proper way to do it would be to introduce a new table with a foreign key back to the contacts table and a column for a cleanser, like (quick'n'dirty example):

CREATE TABLE contacts_verification
(
   contact_id int references contacts(id),
   cleanser int
)

Then if you want to mark a record you just insert the contact id and some sort of cleanser identification (1, 2, 3, 4), or you can use a text field and meaningful names if you really want:

INSERT INTO contacts_verification (contact_id, cleanser) VALUES (21386, 1)

Then just use JOIN to get back the records marked by a cleanser:

SELECT c.*
  FORM contacts c
  JOIN contacts_verification dep_verify
    ON dep_verify.contact_id = c.id
   AND dep_verify.cleanser = 1

Upvotes: 1

Related Questions