Multi-Tenant E-commerce Platform
Consider a multi-tenant e-commerce platform where multiple retailers share the same application infrastructure. Each retailer operates as a separate tenant and manages its own inventory, orders, and customers. Here’s how the database schema might be designed.
Shared Schema Approach: In this approach, all retailers share the same database schema, with each table containing a tenant_id column to differentiate data between tenants.
For example:
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
price NUMERIC,
tenant_id INT
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
product_id INT,
quantity INT,
tenant_id INT
);
Explanation: In the above Query, The SQL statements create two tables: products
and orders
. The products
table stores information about products, including an id
(auto-incrementing primary key), name
(product name), price
(product price), and tenant_id
(identifier for the tenant associated with the product).
The orders
table stores information about orders, including an id
(auto-incrementing primary key), product_id
(foreign key referencing the id
column in the products
table), quantity
(quantity of the product in the order), and tenant_id
(identifier for the tenant associated with the order).
These tables are designed for a multi-tenant architecture, where each tenant has their own set of products and orders, ensuring data isolation and security.
Separate Schema Approach: In this approach, each retailer has its own schema within the same database instance, ensuring complete data isolation. For example:
-- Create a new schema named "retailer1"
CREATE SCHEMA retailer1;
-- Create a new schema named "retailer2"
CREATE SCHEMA retailer2;
-- Create a table named "products" in the "retailer1" schema
CREATE TABLE retailer1.products (
id SERIAL PRIMARY KEY, -- Auto-incrementing primary key
name TEXT, -- Product name (text)
price NUMERIC -- Product price (numeric)
);
-- Create a table named "products" in the "retailer2" schema
CREATE TABLE retailer2.products (
id SERIAL PRIMARY KEY, -- Auto-incrementing primary key
name TEXT, -- Product name (text)
price NUMERIC -- Product price (numeric)
);
Explanation: In the above Query, The SQL statements create two separate schemas, retailer1
and retailer2
, in a database. Each schema represents a separate logical grouping of database objects, such as tables, views, and functions and is used to isolate data and resources for different retailers.
Within each schema, a table named products
is created to store information about the products sold by each retailer.
Each products
table has columns for id
(auto-incrementing primary key), name
(product name), and price
(product price). This schema and table structure enable data separation and organization for multiple retailers within the same database.
Multi-tenant Application Database Design
In the digital age, businesses are increasingly adopting multi–tenant architectures to serve multiple customers or tenants from a single application instance. This approach offers cost efficiency, scalability, and streamlined management. However, designing a robust database schema for multi–tenant applications requires careful consideration of various factors.
In this article, we’ll learn multi-tenant database design, providing insights, examples, and best practices for creating scalable and efficient systems.
Contact Us