0

I have a Vendor table with vendor_Id as the PK and 10 other tables which have reference to Vendor.vendor_Id.

Now, if Vendor chooses to close their account, we need to delete all the data in the other 10 tables.

Is it a good idea to have ON CASCADE DELETE in 10 tables for the vendor_Id field or shall we delete the vendor data one table at a time? Shall we write a Stored Procedure for the same ?

  • Delete any data is bad idea itself. Simply mark the account as non-valid. Or move the data from all tables into separate archive table. – Akina Feb 25 at 8:09
  • @Akina : they need to be implemented as a part of GDPR Compliance Right to Erasure. – j10 Feb 25 at 8:31
  • 2
    If so ON DELETE CASCADE must solve your task. But ensure that all references are 1:N. – Akina Feb 25 at 8:38
  • 10 tables? Over-normalized? – Rick James Mar 2 at 2:53
  • No. They are for different features in a SaaS app. We added vendor_Id field in most of the tables to ease report generation and data isolation for Vendor data. – j10 Mar 2 at 6:31
0

Caveat: Am American that uses Microsoft SQL Server. My opinions are based on my experiences, and may need to be modified to suit your own needs.

I would maybe look at keeping the vendor_id intact, and just replace the information on that record with some filler information. In designing the database, an effort should have been made to keep the vendor information contained to only a few tables, and anywhere else that references that vendor would have been through its id field as a foreign key. This would keep the integrity of the data, especially if you have something like sales records, and really CAN'T delete a bunch of data that is vital to your own business. A vendor table could look like the below.

Vendor_id   Vendor_name   Vendor_phone
----------------------------------------
123         BigSales Co.   555-5555
234         [redacted]     [redacted]
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.