# One Brain, Many Windows: Multi-Store Ecommerce Architecture

> I tried a three-storefront shop on MariaDB: with 40 concurrent buyers, read-then-write stock code sold ten units 27 times while the stock screen read zero. How domain routing, cache keys, composite foreign keys and a view-based seat belt close the leaks between storefronts.

- Author: [Halit Yeşil](https://halityesil.com/en/about/)
- Published: 2026-11-07
- Language: English
- Canonical URL: https://halityesil.com/en/multi-store-ecommerce-architecture/
- Turkish version: https://halityesil.com/multi-store-e-ticaret-mimarisi.md
- Category: Software Architecture
- Tags: caching, ecommerce, foreign key, Host header, inventory, MariaDB, multi-store, multi-tenant, PHP
- License: https://creativecommons.org/licenses/by-nc/4.0/
- Cite as: Halit Yeşil. “One Brain, Many Windows: Multi-Store Ecommerce Architecture”. halityesil.com, 2026-11-07. https://halityesil.com/en/multi-store-ecommerce-architecture/

---

Pointing a second domain at a shop takes one afternoon. New logo, new theme, two lines of `if` on `$_SERVER['HTTP_HOST']`, everyone goes home happy. Then one morning the wholesale price shows up in the retail storefront, or two storefronts sell the same last unit to two different customers.

Multi-store ecommerce architecture is about that second day. Picture three shop windows side by side on a street with one stockroom behind them. Customers look through the glass and read the price tag. The tag changes from window to window, the shelf does not. The domain is the easy part. The real work is answering "who owns this data?" for every table, and not leaving the answer to the good intentions of your code.

Rather than guess where the glass cracks, I tried it in a sandbox: MariaDB 10.11.14, PHP 8.3.6, three storefronts, a million orders, and 40 concurrent buyers going for the same product.

## Is Multi-Store the Same as Multi-Tenant?

Separate the words first, because the schema branches from here.

- **Multi-store:** One owner. Catalogue, stock and admin are shared; brand, price or language differ.
- **Multi-tenant:** Customers who do not know each other live in the same software. If one sees another's data, that is a breach, not a display bug.

Your storefronts may sit somewhere between the two: two companies in one group, two brands selling from one warehouse. So draw the line early. Mature platforms have drawn it in different places:

| Platform | Unit | What varies | Notable limit |
|---|---|---|---|
| [Adobe Commerce](https://experienceleague.adobe.com/en/docs/commerce-admin/start/setup/websites-stores-views) | Site > store > store view | Menu, root category, design, translation | [Price](https://experienceleague.adobe.com/en/docs/commerce-admin/catalog/products/pricing/catalog-price-scope) is global (default) or per site; no per-store price |
| [Saleor](https://docs.saleor.io/developer/channels/overview) | Channel | Product access, variant price, shipping price | One currency per channel; products are not permission-scoped by channel |
| [Sylius](https://docs.sylius.com/the-book/products/pricing) | Channel | Price, in the channel's base currency | One base currency per channel |
| [Medusa](https://docs.medusajs.com/resources/commerce-modules/sales-channel) | Sales channel | Product availability; carts and orders tied to the channel | Stock is read from the locations linked to the channel |

The shared idea: in Saleor and Medusa the warehouse does not belong to the storefront, it is a separate concept the storefront links to. Our schema does the same.

## Inventory: Who Owns Which Data?

Fill in this table before writing code. Filling it in later is billed as a data migration.

| Data | Scope | Why |
|---|---|---|
| Product identity, SKU | Shared | Same physical product |
| Price, visibility | Storefront | The tag belongs to the window |
| Stock | Warehouse | One shelf; the window links to it |
| Cart, order, coupon | Storefront | The storefront owns the sale |
| Payment settings | Storefront | Storefronts may use different merchant accounts |
| Customer account | Your call | Shared is convenient, separate is a boundary. If the storefronts are separate companies, ask a lawyer first |

**Reality check:** `store_id` does not go in every table. Put it on the stock table and you start counting one shelf three times. Data with an owner gets scoped; shared data stays shared on purpose.

```sql
CREATE TABLE store_products (
  store_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  price_minor INT UNSIGNED NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (store_id, product_id),
  FOREIGN KEY (store_id) REFERENCES stores (id),
  FOREIGN KEY (product_id) REFERENCES products (id)
);

CREATE TABLE stock (
  warehouse_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  qty INT NOT NULL,
  PRIMARY KEY (warehouse_id, product_id),
  CHECK (qty >= 0)
);

CREATE TABLE store_warehouses (
  store_id INT UNSIGNED NOT NULL,
  warehouse_id INT UNSIGNED NOT NULL,
  priority TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (store_id, warehouse_id)
);
```

`price_minor` is an integer in minor units. The line price is copied onto the order (`unit_price_minor`), so changing a price tomorrow does not rewrite yesterday's invoice.

## The Door: From Domain to Storefront

Picking the storefront is the first job of every request, and its input is in the attacker's hands, because the client sends the `Host` header. OWASP's [Host Header Injection guide](https://wstg.owasp.org/latest/4-Web_Application_Security_Testing/07-Injection/17-Host_Header_Injection) highlights two outcomes: cache poisoning and password reset links built with the attacker's domain. It also covers going around a validated `Host` with `X-Forwarded-Host`.

**Mistake:** Falling back to a default storefront for unknown domains. That convenience hands the attacker your content and your links.

**Fix:** An exact-match list. Not on the list means `null`, which means 404. `X-Forwarded-Host` is read only when the request comes from your own proxy.

```php
<?php
declare(strict_types=1);

final readonly class Vitrin
{
    public function __construct(
        public int $id,
        public string $kod,
        public string $paraBirimi,
    ) {
    }
}

/** Maps a domain to a storefront. Unknown domains never fall back to a default. */
final readonly class VitrinCozucu
{
    /**
     * @param array<string, Vitrin> $alanlar           domain => storefront
     * @param list<string>          $guvenilirVekiller your own proxy IPs only
     */
    public function __construct(
        private array $alanlar,
        private array $guvenilirVekiller = [],
    ) {
    }

    /** @param array<string, string> $server $_SERVER */
    public function coz(array $server): ?Vitrin
    {
        $kaynak = $server['HTTP_HOST'] ?? '';

        if (
            isset($server['HTTP_X_FORWARDED_HOST'])
            && in_array($server['REMOTE_ADDR'] ?? '', $this->guvenilirVekiller, true)
        ) {
            $kaynak = $server['HTTP_X_FORWARDED_HOST'];
        }

        $alan = strtolower(rtrim((string) preg_replace('/:\d+$/', '', $kaynak), '.'));

        return $this->alanlar[$alan] ?? null;
    }
}
```

The identifiers are Turkish because the code is the same as the Turkish edition (`vitrin` means storefront). I tried eleven inputs. `MAGAZA-A.test:443` and the trailing-dot `magaza-a.test.` reached the right storefront. `magaza-a.test.evil.test`, `evil.test:80@magaza-a.test` and an empty header returned `null`. `X-Forwarded-Host` from an outside IP was ignored. The chain `export-a.test, evil.test` from the trusted proxy also returned `null`, because exact match does not negotiate.

## The Glass: Leaks Between Storefronts

In a shared database, isolation hangs on one `WHERE` clause. Microsoft's [multitenant data guide](https://learn.microsoft.com/azure/architecture/guide/multitenant/approaches/storage-data) describes the shared database as the cheapest option and adds the same debt: you need to plan so tenants cannot reach each other's data. A forgotten `store_id` throws no error. It shows the wrong data in perfectly good formatting.

So I use three layers of glass. Each covers the day the previous one was forgotten.

### Layer one: make the storefront a dependency, not a parameter

If the class that writes queries receives the storefront in its constructor, forgetting it stops depending on anyone's attention. The cache works the same way: the class writes the key prefix, not the caller.

```php
interface AnahtarDegerDeposu
{
    public function oku(string $anahtar): ?string;

    public function yaz(string $anahtar, string $deger, int $saniye): void;
}

/** The class writes the key prefix, not the caller, so the storefront cannot be forgotten. */
final readonly class VitrinOnbellegi
{
    public function __construct(
        private AnahtarDegerDeposu $depo,
        private Vitrin $vitrin,
    ) {
    }

    public function oku(string $anahtar): ?string
    {
        return $this->depo->oku($this->tamAnahtar($anahtar));
    }

    public function yaz(string $anahtar, string $deger, int $saniye = 300): void
    {
        $this->depo->yaz($this->tamAnahtar($anahtar), $deger, $saniye);
    }

    private function tamAnahtar(string $anahtar): string
    {
        return sprintf('vitrin:%d:%s:%s', $this->vitrin->id, $this->vitrin->paraBirimi, $anahtar);
    }
}

final readonly class SiparisDeposu
{
    public function __construct(
        private PDO $pdo,
        private Vitrin $vitrin,
    ) {
    }

    /** @return list<array<string, mixed>> */
    public function sonSiparisler(int $adet = 20): array
    {
        $sorgu = $this->pdo->prepare(
            'SELECT id, created_at FROM orders
             WHERE store_id = :vitrin
             ORDER BY created_at DESC
             LIMIT :adet'
        );
        $sorgu->bindValue(':vitrin', $this->vitrin->id, PDO::PARAM_INT);
        $sorgu->bindValue(':adet', $adet, PDO::PARAM_INT);
        $sorgu->execute();

        return $sorgu->fetchAll(PDO::FETCH_ASSOC);
    }
}
```

In the test the `ana` storefront wrote `49900` to `urun:10:fiyat`, and `export`, reading the same backing store, got `null`. The key landed as `vitrin:1:TRY:urun:10:fiyat`. Check separately that your page cache and CDN keys include the domain.

Indexes are affected too. The first column of a composite index is usually `store_id`, so the equalities-first rule from my [MariaDB composite index post](https://halityesil.com/en/mariadb-composite-index-order/) turns into `(store_id, created_at)` here.

### Layer two: tie the foreign key to the storefront

A classic schema links `order_items.order_id` to the order and never asks which storefront the line belongs to. One bug can write a product from one storefront into another's order. A composite foreign key closes that in the schema:

```sql
ALTER TABLE orders ADD UNIQUE KEY uq_store_id (store_id, id);

CREATE TABLE order_items (
  store_id INT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  qty INT UNSIGNED NOT NULL,
  unit_price_minor INT UNSIGNED NOT NULL,
  PRIMARY KEY (store_id, order_id, product_id),
  CONSTRAINT fk_item_order FOREIGN KEY (store_id, order_id)
    REFERENCES orders (store_id, id),
  CONSTRAINT fk_item_product FOREIGN KEY (store_id, product_id)
    REFERENCES store_products (store_id, product_id)
);
```

I tried adding a line to storefront 2's order with storefront 1's id: `ERROR 1452`, `fk_item_order`. I tried a product storefront 2 does not sell: `1452` again, this time `fk_item_product`. The database now refuses the cross-storefront row instead of waiting for your attention.

**Technical detail:** This needs a unique index on `(store_id, id)` in `orders`. Without it the table is not created at all: `errno: 150 "Foreign key constraint is incorrectly formed"`.

### Layer three: a view as a seat belt in MariaDB

PostgreSQL hands this to the engine with [row security policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html). On MariaDB 10.11, `CREATE POLICY` gets you `ERROR 1064`. You can build a rough equivalent from a view, a function and grants. Using `@vitrin_id` directly does not work: the [CREATE VIEW documentation](https://mariadb.com/docs/server/server-usage/views/create-view) forbids variables and the attempt gives `ERROR 1351`. A function gets around that:

```sql
CREATE OR REPLACE FUNCTION aktif_vitrin() RETURNS INT UNSIGNED
  DETERMINISTIC READS SQL DATA
  RETURN @vitrin_id;

CREATE OR REPLACE SQL SECURITY DEFINER VIEW vitrin_orders AS
  SELECT id, store_id, customer_email, created_at
  FROM orders
  WHERE store_id = aktif_vitrin()
  WITH CHECK OPTION;

GRANT SELECT, INSERT ON vitrin.vitrin_orders TO 'vitrin_app'@'localhost';
```

The application user has no rights on the base table. Results:

- With no variable set, the query returned 0 rows. The door is closed by default.
- Reading `orders` directly gave `ERROR 1142`.
- An `INSERT` for another storefront gave `ERROR 1369 CHECK OPTION failed`.

**The hard part:** the word `DETERMINISTIC` is not decoration. I measured the last 20 records out of a million orders, three runs each:

| Scope | Plan | Index entries read | Pages | Time |
|---|---|---|---|---|
| Direct `store_id = 2` | ref | 20 | 3 | 0.04-0.05 ms |
| View, `DETERMINISTIC` | ref | 20 | 3 | 0.07-0.12 ms |
| View, `NOT DETERMINISTIC` | index | 1,000,001 | 3,713 | 1,760-2,128 ms |
| View, subquery + `NOT DETERMINISTIC` | index | 1,000,001 | 3,713 | 1,456-2,288 ms |

With the default `NOT DETERMINISTIC`, the same query ran tens of thousands of times slower and walked the whole index. There is a catch: this function reads a session variable, so by the definition in the [CREATE FUNCTION documentation](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-function) it is not truly deterministic, and the documentation warns that such a declaration may give incorrect results. I ran one prepared statement four times while switching storefronts, then four more times with the query cache on, and the counts were always right. That does not mean "safe". It means "did not break in these scenarios".

Two more limits. The application sets the variable, so anyone who finds SQL injection can run `SET @vitrin_id = 1`; I tried it and the view opened the other storefront. That is why it is a seat belt and not a wall. Second, persistent connections: with `PDO::ATTR_PERSISTENT` the same connection arrived at the second "request" still carrying the previous `@vitrin_id`. If you use them, set the variable at the start of every request and reset it to `NULL` at the end.

## The Stockroom: Three Windows, One Last Unit

Shared stock means buyers run for the same shelf through different doors. I set stock to 10, sent 40 concurrent PHP processes at it, and ran each method five times. The sandbox has two cores.

| Method | Sold (5 runs) | Errors | Stock left |
|---|---|---|---|
| Read, compute, write `SET qty = ?` | 12, 19, 27, 15, 19 | None | 0 |
| Read, write `SET qty = qty - 1` | 10 | `4025` for 1-3 buyers | 0 |
| `SELECT ... FOR UPDATE` | 10 | None | 0 |
| Single conditional `UPDATE` | 10 | None | 0 |

Read the first row carefully. Ten units sold 27 times, and the stock screen showed a clean zero. Overselling does not appear in the report. It comes back as a phone call.

`CHECK (qty >= 0)` saved the second row. Sales were right, but some buyers got `Error 4025`. A constraint is a good last line of defence and a poor way to control flow.

The third and fourth rows are correct. For a single product, the conditional `UPDATE` is simpler:

```php
/** Stock decrement is one statement: one affected row means the unit is yours. */
function stokDus(PDO $pdo, int $depoId, int $urunId, int $adet): bool
{
    $sorgu = $pdo->prepare(
        'UPDATE stock SET qty = qty - :adet
         WHERE warehouse_id = :depo AND product_id = :urun AND qty >= :gerekli'
    );
    $sorgu->execute([
        ':adet' => $adet,
        ':depo' => $depoId,
        ':urun' => $urunId,
        ':gerekli' => $adet,
    ]);

    return $sorgu->rowCount() === 1;
}
```

Run it inside the same transaction as the order and payment records, and roll back on `false`. For goods coming in, our old friend [ON DUPLICATE KEY UPDATE](https://halityesil.com/en/mysql-with-insert-into-on-duplicate-key-update-advantages-and-disadvantages-of-a-fun-approach/) does the job.

## How Does a Search Engine See Three Windows?

The same product with the same copy on three domains is a duplicate. Google's [multi-regional sites guide](https://developers.google.com/search/docs/specialty/international/managing-multi-regional-sites) says to pick a preferred version of similar same-language content with `rel="canonical"` and add `hreflang`. Per-storefront sitemaps, `robots.txt` and canonical tags should come from the storefront `VitrinCozucu` returned. Build the canonical from `HTTP_HOST` and you reopen in the page head the hole you closed at the door.

## Where Does the Rule Go When an Agent Writes the Code?

A coding agent writing a new report query is only as good as the pattern it copies. Put "every query and cache key is scoped to the storefront, except the stock table" in your [AGENTS.md file](https://halityesil.com/en/vibe-coder-4-2-agents-md-vs-claude-md/); Claude Code, Codex, Cursor and Antigravity all read it, each by its own rules. A rule file is context, not a guarantee. The door is still held by the composite foreign key and by leak tests: a few tests that create two storefronts and look for one's records on the other's screen.

## When Do You Need Separate Databases?

Everything above is for three storefronts in one database. Microsoft's distinction helps here: a shared database is the cheapest and densest model, but it carries noisy neighbour risk and struggles with differing backup or location requirements.

| Situation | Choice |
|---|---|
| One company, shared warehouse, shared admin | One database, `store_id` |
| Separate companies, order data must stay apart | One core, discuss a database per storefront |
| Per-tenant backup, location or encryption demands | Database per tenant |
| One storefront's traffic chokes the others | Queries and cache first, then sharding |

Separate databases also make shared stock harder. Counting one shelf from two databases is a post of its own.

Cleaning the glass is easy. The work is knowing who counts the shelf behind it. Leave the tag to the window, the shelf to the warehouse and the arithmetic to the database, and you will not be the one phoning customer number 27.