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:

  • Law
  • 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 Consent between User and DataCollectionPurpose.

  1. Should I fill it only with data where user actually gave consent.
  2. 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

      ID int
      , RuleName varchar(30)
      , IsGlobal bit

      ID int
      , [Name] varchar(30)

      PersonID int
      , RuleID int

INSERT INTO dbo.Person (ID, [Name])
      (1, 'Tom')
     , (2, 'Richard')
     , (3, 'Harry')

INSERT INTO dbo.[Rule] (ID, RuleName, IsGlobal)
      (1, 'LocalRule', 0)
    , (2, 'GlobalRule', 1);

INSERT INTO dbo.PersonRule (PersonID, RuleID)
     (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
SELECT P.*, r.* FROM dbo.[Rule] r
OUTER APPLY dbo.Person p
WHERE r.IsGlobal = 1
  • 3
    If you create an mcve and add it to the question you may (a) figure out which one you prefer, or (b) get a great answer showing why one of the two is the best choice. – Max Vernon Jul 9 '18 at 12:31
  • Depends in a lot of considerations, also you can have a user table in this case you can have a "Jonh Doe" user flagged as "represents everyone/anyone" or just let that field nullable. The last solution will demand left outter joins but both has good and bad sides, do you need a opnion based answer? – jean Jul 9 '18 at 12:58

You can use solution 2, but you should have a stronger INSERT logic validation, to check whether data has already a policy or not.

So it would be something like:

  • check for law rules first (applies to all your users) check for user

  • consent rule (user specific, so you have to relate it to userID)

So you can use tables: User, DataCollectionPurpose(a flag for law or user consent) and Consent(so you will join to it where your flag in the other table is for user consent)

Hope this helps!

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.