Natuurlijk! Hieronder vind je een overzichtelijke aanpak voor het ontwerpen van een multi-tenant SQL-database die efficiënt meerdere huurders ondersteunt, inclusief isolatie, tabelstructuren en relaties. 1. Multi-tenant strategie - **Gescheiden schema’s per tenant:** Elke tenant krijgt een eigen schema. Voordeel: volledige isolatie, eenvoudig beheer. Nadeel: minder schaalbaar bij veel tenants. - **Single database met tenant ID:** Alle data in één tabel met een kolom voor tenant_id. Voordeel: schaalbaar, eenvoudiger beheer; nadeel: extra waarborging voor isolatie. Voor grote schaal en eenvoudige onderhoudbaarheid wordt meestal de tweede aanpak gebruikt. --- ### 1. Tabellenstructuur met tenant_id **Algemene principes:** - Alle tabellen bevatten een `tenant_id` kolom. - Bij elke query wordt `tenant_id` meegegeven om data te filteren. - Maak gebruik van views of procedures om data te isoleren. --- ### 2. Voorbeeld SQL-structuur ```sql -- Tabel voor huurders (tenants) CREATE TABLE tenants ( id INT PRIMARY KEY AUTO_INCREMENT, naam VARCHAR(255) NOT NULL, contact_email VARCHAR(255), -- andere relevante informatie created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Gebruikers tabel CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, tenant_id INT NOT NULL, naam VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, wachtwoord_hash VARCHAR(255) NOT NULL, rol VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (tenant_id) REFERENCES tenants(id) ); -- Producten tabel CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, tenant_id INT NOT NULL, naam VARCHAR(255) NOT NULL, beschrijving TEXT, prijs DECIMAL(10,2), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (tenant_id) REFERENCES tenants(id) ); -- Bestellingen tabel CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, tenant_id INT NOT NULL, user_id INT NOT NULL, order_datum TIMESTAMP DEFAULT CURRENT_TIMESTAMP, totaal_bedrag DECIMAL(10,2), status VARCHAR(50), FOREIGN KEY (tenant_id) REFERENCES tenants(id), FOREIGN KEY (user_id) REFERENCES users(id) ); -- Orderregels (producten in order) CREATE TABLE order_items ( id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, product_id INT NOT NULL, hoeveelheid INT NOT NULL, prijs DECIMAL(10,2), FOREIGN KEY (order_id) REFERENCES orders(id), FOREIGN KEY (product_id) REFERENCES products(id) ); ``` --- ### 3. Isolatie en veiligheid - Bij elke query filter je op `tenant_id`. Bijvoorbeeld: ```sql SELECT * FROM products WHERE tenant_id = ?; ``` - Gebruik parameter binding in je applicatie voor `tenant_id`. - Overweeg het gebruik van database-rollen en privileges om toegang te beperken. --- ### 4. Schaalbaarheid en onderhoudbaarheid - **Indexering:** Zorg voor indexen op `tenant_id` en vaak gebruikte kolommen voor snelle queries. - **Partitionering:** Voor grote datasets kan partitionering op `tenant_id` helpen. - **Automatisering:** Scripts voor het aanmaken van nieuwe tenants, inclusief alle benodigde tabellen en standaardgegevens. - **Back-up en herstel:** Regelmatige back-ups van de volledige database, met aandacht voor tenant-specifieke data. - **Monitoring:** Houd prestaties en query-efficiëntie in de gaten. --- ### 5. Alternatief: schema-per-tenant Voor kleinere toepassingen of met strenge isolatie-eisen: ```sql CREATE SCHEMA tenant1; CREATE TABLE tenant1.users ( ... ); -- Herhaal per tenant ``` Maar dit wordt minder schaalbaar bij veel tenants. --- **Samenvatting:** Gebruik een enkele database met een `tenant_id` kolom in elke tabel voor schaalbare, onderhoudsvriendelijke isolatie. Zorg voor goede indexering, beveiliging en automatisering om schaalbaarheid en onderhoudbaarheid te garanderen.

Hier is een uitgebreide SQL-database structuur voor multi-tenant ondersteuning: ## Hoofdstructuur en Tenant Isolatie ```sql -- Hoofd schema voor tenant management CREATE SCHEMA IF NOT EXISTS tenant_management; -- Tenant basisinformatie CREATE TABLE tenant_management.tenants ( tenant_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_name VARCHAR(100) NOT NULL UNIQUE, domain VARCHAR(255) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, is_active BOOLEAN DEFAULT TRUE, config JSONB ); -- Gebruikers met tenant relatie CREATE TABLE tenant_management.users ( user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenant_management.tenants(tenant_id), email VARCHAR(255) NOT NULL, password_hash VARCHAR(255) NOT NULL, first_name VARCHAR(100), last_name VARCHAR(100), role VARCHAR(50) DEFAULT 'user', is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE(tenant_id, email) ); CREATE INDEX idx_users_tenant ON tenant_management.users(tenant_id); ``` ## Tenant-specifieke Schema's ```sql -- Dynamisch schema per tenant (optie 1 - Aanbevolen voor sterke isolatie) CREATE OR REPLACE FUNCTION create_tenant_schema(tenant_uuid UUID) RETURNS VOID AS $$ DECLARE schema_name TEXT; BEGIN schema_name := 'tenant_' || REPLACE(tenant_uuid::text, '-', '_'); EXECUTE 'CREATE SCHEMA IF NOT EXISTS ' || schema_name; -- Producten tabel EXECUTE format(' CREATE TABLE IF NOT EXISTS %I.products ( product_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name VARCHAR(200) NOT NULL, description TEXT, price DECIMAL(10,2) NOT NULL, stock_quantity INTEGER DEFAULT 0, is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP )', schema_name); -- Orders tabel EXECUTE format(' CREATE TABLE IF NOT EXISTS %I.orders ( order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL, total_amount DECIMAL(10,2) NOT NULL, status VARCHAR(50) DEFAULT ''pending'', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP )', schema_name); -- Order items tabel EXECUTE format(' CREATE TABLE IF NOT EXISTS %I.order_items ( order_item_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), order_id UUID NOT NULL, product_id UUID NOT NULL, quantity INTEGER NOT NULL, unit_price DECIMAL(10,2) NOT NULL, FOREIGN KEY (order_id) REFERENCES %I.orders(order_id) )', schema_name, schema_name); END; $$ LANGUAGE plpgsql; ``` ## Alternatieve Aanpak: Shared Schema met Tenant ID ```sql -- Alternatief: Gedeelde tabellen met tenant_id (minder isolatie maar eenvoudiger) CREATE SCHEMA IF NOT EXISTS shared_data; CREATE TABLE shared_data.products ( product_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenant_management.tenants(tenant_id), name VARCHAR(200) NOT NULL, description TEXT, price DECIMAL(10,2) NOT NULL, stock_quantity INTEGER DEFAULT 0, is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE(tenant_id, name) ); CREATE TABLE shared_data.orders ( order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenant_management.tenants(tenant_id), user_id UUID NOT NULL, total_amount DECIMAL(10,2) NOT NULL, status VARCHAR(50) DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX idx_products_tenant ON shared_data.products(tenant_id); CREATE INDEX idx_orders_tenant ON shared_data.orders(tenant_id); ``` ## Beveiliging en Toegangscontrole ```sql -- ROW LEVEL SECURITY (RLS) voor extra beveiliging ALTER TABLE shared_data.products ENABLE ROW LEVEL SECURITY; ALTER TABLE shared_data.orders ENABLE ROW LEVEL SECURITY; -- Beleid voor products CREATE POLICY tenant_products_policy ON shared_data.products USING (tenant_id = current_setting('app.current_tenant')::UUID); -- Beleid voor orders CREATE POLICY tenant_orders_policy ON shared_data.orders USING (tenant_id = current_setting('app.current_tenant')::UUID); ``` ## Gebruiksvoorbeelden ```sql -- Nieuwe tenant aanmaken INSERT INTO tenant_management.tenants (tenant_name, domain) VALUES ('Example Bedrijf', 'example.uwapp.nl'); -- Schema aanmaken voor tenant SELECT create_tenant_schema('123e4567-e89b-12d3-a456-426614174000'); -- Product toevoegen (shared approach) INSERT INTO shared_data.products (tenant_id, name, price, stock_quantity) VALUES ('123e4567-e89b-12d3-a456-426614174000', 'Test Product', 29.99, 100); -- Query voor tenant-specifieke data SELECT * FROM shared_data.products WHERE tenant_id = '123e4567-e89b-12d3-a456-426614174000'; ``` ## Schaalbaarheid en Onderhoud ### Prestatie Optimalisaties: ```sql -- Partitionering voor grote datasets CREATE TABLE shared_data.orders_2024 PARTITION OF shared_data.orders FOR VALUES FROM ('2024-01-01') TO ('2025-01-01'); -- Materialized views voor rapportage CREATE MATERIALIZED VIEW tenant_management.tenant_stats AS SELECT t.tenant_id, t.tenant_name, COUNT(DISTINCT u.user_id) as user_count, COUNT(DISTINCT o.order_id) as order_count FROM tenant_management.tenants t LEFT JOIN tenant_management.users u ON u.tenant_id = t.tenant_id LEFT JOIN shared_data.orders o ON o.tenant_id = t.tenant_id GROUP BY t.tenant_id, t.tenant_name; ``` ### Onderhoudsprocedures: ```sql -- Automatisch schema cleanup voor inactieve tenants CREATE OR REPLACE FUNCTION cleanup_inactive_tenants() RETURNS VOID AS $$ DECLARE tenant_record RECORD; BEGIN FOR tenant_record IN SELECT tenant_id FROM tenant_management.tenants WHERE is_active = FALSE AND created_at < NOW() - INTERVAL '30 days' LOOP -- Archiveer data en verwijder schema PERFORM archive_tenant_data(tenant_record.tenant_id); END LOOP; END; $$ LANGUAGE plpgsql; ``` ## Aanbevelingen voor Implementatie: 1. **Sterke Isolatie**: Gebruik aparte schema's per tenant voor maximale gegevensisolatie 2. **Indexering**: Zorg voor uitgebreide indexering op tenant_id en veelgebruikte queryvelden 3. **Backup Strategie**: Implementeer tenant-specifieke backups 4. **Monitoring**: Houd tenant-specifieke prestatiestatistieken bij 5. **Migrations**: Gebruik schema migration tools voor consistente updates Deze structuur biedt een goede balans tussen isolatie, prestaties en onderhoudbaarheid voor een multi-tenant applicatie.