SaaS (Software as a Service)

When building a SaaS (Software as a Service) project, the database architecture is one of the most important decisions.
There are two common approaches to multi‑tenant SaaS database design:

Option 1:
One database per client (separate database per tenant)
How it works:
Each client (tenant) gets their own database.

Your application (same codebase) connects dynamically to the tenant’s database based on their domain, subdomain, or login.

Pros:

• Strong data isolation – clients’ data is completely separated.
• Easier to migrate/export data or comply with regulations (e.g., GDPR).
• Less risk that a bug or query exposes other tenants’ data.
• You can scale some clients separately if needed (e.g., move a big client’s DB to a bigger server).

Cons:

• Harder to manage upgrades or schema changes (you must migrate many databases).
• Higher infrastructure cost (many DB instances).
• More complex DevOps (backups, monitoring, migrations for each DB).

Option 2:
All clients share the same database (single database, multi‑tenant)
How it works:
One database contains all tenants’ data.

Every table that stores tenant-specific data has a tenant_id (or company_id) column to separate data logically.

Pros:

• Easier to manage (only one DB schema to upgrade).
• Cheaper to host (one DB server).
• Easier to run analytics across tenants.
• Simpler CI/CD pipeline.

Cons:

• Security risk if not done carefully – you must always filter by tenant_id in every query, otherwise data leaks between tenants.
• Harder to export one client’s data separately.
• A large single DB can become a bottleneck if you have thousands of tenants.

What to consider if using a single database:

• Add tenant_id to all tenant-specific tables.
• Apply row-level security or strict query filters (e.g., in Laravel use global scopes or middleware).
• Use indexes on tenant_id to keep queries fast.
• Be careful with shared tables (like product catalog) vs tenant-specific tables (like orders).
• Backups: you’re backing up everyone’s data together, so plan recovery carefully.
• Scaling: eventually you may need read replicas, sharding by tenant_id, or moving heavy tenants to their own DB.

Which one should you choose:

• If you expect a few big enterprise clients that might demand strict isolation or their own backups → use separate databases per client.
• If you expect many small clients (e.g., hundreds/thousands of small shops) → use one shared database with tenant_id (multi‑tenant).

Single DB:

• Add tenant_id to tables.
• Use middleware to set where('tenant_id', $tenantId) globally.

Multi DB:

• Have a tenants table in a main DB storing DB connection info (host, username, password).
• On request, resolve the tenant and dynamically set the DB connection (using DB::purge() / DB::reconnect()).