I couldn't come up with better title.
I'm preparing data structure for GDPR and I ran into one problem.
I have two types of legal basis for storing the personal data:
- User specific (consent)
So some of the purposes for collecting data apply to all users (law) and some of them apply only to users which gave consent.
I have relation table
- Should I fill it only with data where user actually gave consent.
- Should I fill it with "fake" data because law applies to user whether they gave consent or not.
Solution 1 will make me write two separate queries and union them, solution 2 will fill table with loads of redundant data.
EDIT: Added code for testing
/* CREATE TABLE [Rule] ( ID int , RuleName varchar(30) , IsGlobal bit ); CREATE TABLE Person ( ID int , [Name] varchar(30) ); CREATE TABLE PersonRule ( PersonID int , RuleID int ); INSERT INTO dbo.Person (ID, [Name]) VALUES (1, 'Tom') , (2, 'Richard') , (3, 'Harry') INSERT INTO dbo.[Rule] (ID, RuleName, IsGlobal) VALUES (1, 'LocalRule', 0) , (2, 'GlobalRule', 1); INSERT INTO dbo.PersonRule (PersonID, RuleID) VALUES (1, 1) , (2, 1) /* drop table PersonRule; drop table Person; drop table [Rule]; */ */ SELECT P.*, r.* FROM dbo.Person p JOIN dbo.PersonRule pr ON P.ID = pr.PersonID JOIN dbo.[Rule] r ON pr.RuleID = r.ID UNION ALL SELECT P.*, r.* FROM dbo.[Rule] r OUTER APPLY dbo.Person p WHERE r.IsGlobal = 1