GDPR-Compliant Database Design: Technical Architecture and Schema Guide

GDPR-Compliant Database Design: Data Protection by Design and Technical Schema Architecture

The European Union General Data Protection Regulation (GDPR) does not consider it sufficient for companies to take only administrative and legal measures when processing personal data. Article 25 of the Regulation (Data Protection by Design and by Default) requires that data protection principles be integrated into the system at the software development stage. Accordingly, designing the database architecture, which is the heart of the system, in a GDPR-compliant manner is vital both for reducing penalty risks and gaining user trust.

In this technical guide, we will address how to design a compliant database schema with concrete examples based on official GDPR articles and European Data Protection Board (EDPB) guidelines.

1. Official Legal Basis and Reflections in Database Architecture

There are four fundamental principles we need to base on when designing a database under GDPR (Article 5):

• Data Minimisation (Article 5(1)(c)): Only data that is absolutely necessary for the processing purpose should be kept in the database. Extra columns should not be added with the thought of "maybe we'll use it in the future."
• Storage Limitation (Article 5(1)(e)): Personal data should not be stored longer than necessary for the processing purpose. The database architecture should support automatic data purging or anonymization processes.
• Integrity and Confidentiality (Article 32): Encryption and access control must be applied at the database level to prevent unauthorized access to personal data.
• Accountability (Article 5(2)): It must be verifiable which data was processed, when, by whom, and based on which consent declaration.

2. GDPR Compliance Steps in Technical Architecture

A. Separation of Personal Data (PII) and Pseudonymisation

Pseudonymisation, as defined in GDPR Recital 26, means processing personal data in such a manner that it cannot be attributed to a specific data subject without the use of additional information.

The most effective way to ensure this at the database level is to separate columns containing PII (Personally Identifiable Information - name, surname, email, phone, etc.) from main operational tables and store them in isolated tables with different access privileges.

B. Implementation of Right to Erasure (Article 17)

When users request deletion of their data, one of the most common mistakes technical teams make is using only an is_deleted = true flag (soft delete) in the database. From a legal perspective, soft delete does not comply with GDPR Article 17 because it does not actually delete the data from the system.

When a user is deleted, one of the following two methods must be applied:

1. Hard Delete (Physical Deletion): Permanent deletion of all PII rows belonging to the user from the database (using ON DELETE CASCADE or special triggers in related tables).
2. Irreversible Anonymization: Overwriting fields identifying the user with random hash values or empty data (such as making the email address deleted_user_1234@anon.com). This method is preferred so that past financial reports and statistics are not corrupted.

C. Consent Management (Article 7) and Audit Trail

It is a legal requirement to be able to prove on which date users approved which version of the Privacy Policy. For this purpose, an independent log table that records each consent operation with a timestamp and IP address must exist in the database.

3. Example SQL Schema: GDPR-Compliant User Structure

The PostgreSQL schema below shows a professional database architecture where personal data is separated from operational data, consent management is tracked, and data storage periods are appropriately configured.

-- STEP 1: Main Users Table (does not contain PII, only relational ID and pseudonym)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    pseudonym_id VARCHAR(50) UNIQUE NOT NULL, -- Anonymous ID to be given to external analytics tools
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- STEP 2: Personal Data (PII) Table (encrypted and isolated)
CREATE TABLE user_profiles_pii (
    user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    -- Sensitive columns should be stored encrypted
    encrypted_first_name BYTEA NOT NULL,
    encrypted_last_name BYTEA NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone_number VARCHAR(50),
    birth_date DATE,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- STEP 3: Consent Record Table (Article 7 Compliance Log)
CREATE TABLE user_consents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    policy_version VARCHAR(10) NOT NULL, -- e.g., 'v2.1'
    consent_given BOOLEAN DEFAULT FALSE,
    given_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45) NOT NULL, -- 45 characters for IPv4 or IPv6 support
    user_agent TEXT
);

-- STEP 4: Transactional Data (does not contain personal data, kept for statistical purposes)
CREATE TABLE user_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE SET NULL, -- Transaction data remains even if user is deleted, becomes anonymous
    amount DECIMAL(10, 2) NOT NULL,
    transaction_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Advantages of Schema Design:

1. ON DELETE CASCADE: When you delete a user record (DELETE FROM users WHERE id = ...), the user's personal information (user_profiles_pii) such as name, surname, and email, and consent history are automatically physically deleted from the database.
2. ON DELETE SET NULL: When the user is deleted, financial transactions are not deleted, but the user_id field is set to NULL, making the transaction data completely anonymous. Thus, your turnover and accounting reports remain intact while fully complying with GDPR Article 17.
3. Pseudonymisation: The pseudonym_id in the users table is given to third-party analytics tools or data scientists, thus keeping real identities confidential.

4. Security and Data Protection Obligations (Article 32)

Encryption at the database level must be applied at two main layers:

1. Encryption at Rest: In case the database server is physically stolen or the disk is copied, the entire data disk must be encrypted (using AWS KMS or PostgreSQL transparent data encryption).
2. Column-Level Encryption: Even if the database is leaked in application-level attacks such as SQL injection, sensitive data such as names and surnames should be encrypted with strong algorithms like AES-256 at the application layer and written to the database as BYTEA type so that they cannot be read.

5. Frequently Asked Questions (FAQ)

Question 1: Is it GDPR compliant to delete personal data in the database with "soft delete" (is_deleted flag)?

Answer: No. Under GDPR Article 17 (Right to Erasure), the data must be actually deleted from the system or made irreversibly anonymous. Using an is_deleted flag that only closes visibility does not meet the legal requirement because the data is still readable on the disk.

Question 2: Does pseudonymisation completely anonymize the data?

Answer: No. According to GDPR Recital 26, pseudonymized data retains its status as personal data if identity can still be determined by combining additional information or tables. Complete anonymization can only be achieved by making the data irreversibly unrecoverable.

Question 3: Can personal data be kept in database logs (Audit trail)?

Answer: No. Unencrypted PII (name, email, password, etc.) should not be present in log records kept for error analysis or auditing purposes. Only the identity of the operation (User ID), time, and operation type should be kept in logs; sensitive data should be masked.

Conclusion

GDPR-compliant database design is not only a legal obligation but also a factor that directly affects the penalty a company may receive in case of a possible data breach. By adhering to the principle of data protection by design, establishing structures that isolate PII data, automate the right to erasure technically with cascade structures, and log consent history in an unchangeable way is the cornerstone of a sustainable software development process.