Multi-Website Configuration

Overview

Manage different customer prices across multiple websites within your Magento installation. Set region-specific pricing, handle multi-currency scenarios, and maintain separate price lists for different storefronts.

How It Works

The website_id field determines which website(s) a price applies to:

website_id = 0: Price applies to all websites (global)
website_id = 1, 2, 3, etc.: Price applies only to that specific website

When resolving prices, the system:

Checks for prices with matching website_id
Falls back to website_id = 0 if no website-specific price exists
Applies standard catalog price if no customer price matches

Website ID Reference

Finding Website IDs

Admin Method:

Navigate to: Stores >All Stores

View your website structure:

Main Website (ID: 1) └─ Main Store └─ English Store View └─ German Store View EU Website (ID: 2) └─ EU Store └─ EU English View └─ EU German View US Website (ID: 3) └─ US Store └─ US English View

Database Query:

SELECT website_id, code, name FROM store_website;

Example output:

| website_id | code | name | |------------|--------|--------------| | 0 | admin | Admin | | 1 | base | Main Website | | 2 | eu | EU Website | | 3 | us | US Website |

Use Cases

Regional Pricing Strategy

Scenario: Different prices for US and EU customers

US Website (website_id=3): Customer: ACME Corp Product: Widget ABC Quantity: 1 Price: $100.00 Website: US Website EU Website (website_id=2): Customer: ACME Corp (same customer) Product: Widget ABC Quantity: 1 Price: €90.00 Website: EU Website

Same customer sees different prices based on which website they're browsing.

Multi-Currency Handling

Example: Product with different currency pricing

Main Website (USD): - Price: $100.00 - Website ID: 1 UK Website (GBP): - Price: £75.00 - Website ID: 4 EU Website (EUR): - Price: €85.00 - Website ID: 2

Global Price with Regional Overrides

Scenario: Default price everywhere, but cheaper in specific region

Global Default: - Customer: Beta Ltd - Product: Widget ABC - Quantity: 1 - Price: $100.00 - Website: All Websites (0) EU Override: - Customer: Beta Ltd - Product: Widget ABC - Quantity: 1 - Price: €80.00 - Website: EU Website (2)

Browsing EU website: €80.00 Browsing any other website: $100.00

Admin Setup

Step 1: Create Website-Specific Price

Navigate to: Catalog >Customer Prices > Add New

Step 2: Fill Basic Information

Customer: Select customer Product: Select product Quantity: 1 Price: 100.00

Step 3: Select Website

Website Dropdown Options:

All Websites (ID: 0)
Main Website (ID: 1)
EU Website (ID: 2)
US Website (ID: 3)

Select the specific website this price should apply to.

Step 4: Save

Click Save to create the website-specific price.

Advanced Scenarios

Per-Website Quantity Tiers

Example: Different tier structure by region

US Website (website_id=3): qty=1, price=$100, website_id=3 qty=50, price=$95, website_id=3 qty=100, price=$90, website_id=3 EU Website (website_id=2): qty=1, price=€85, website_id=2 qty=25, price=€80, website_id=2 qty=75, price=€75, website_id=2

Same customer, same product, but different tier breakpoints and currency per region.

Time-Based Regional Campaigns

Example: Seasonal promotion only in EU

EU Q1 Campaign (website_id=2): - qty=1 - price=€70 - from_date=2025-01-01 - to_date=2025-03-31 - website_id=2 US Standard (website_id=3): - qty=1 - price=$95 - from_date=NULL - to_date=NULL - website_id=3

EU customers see promotional price Jan-Mar, US customers see standard price year-round.

Shared Customers Across Websites

Important: If a customer can access multiple websites, they can have different prices on each:

Customer ID: 456 (Global B2B Customer) On US Website: - Sees $100.00 (website_id=3 price) On EU Website: - Sees €85.00 (website_id=2 price) On APAC Website: - Sees $110.00 (website_id=4 price)

Database Structure

Schema

CREATE TABLE pricesystem_customerprice ( id INT PRIMARY KEY AUTO_INCREMENT, entity_id INT NOT NULL, customer_id INT NOT NULL, qty DECIMAL(12,4) NOT NULL, value DECIMAL(12,4) NOT NULL, from_date DATE NULL, to_date DATE NULL, website_id INT NOT NULL DEFAULT 0, CONSTRAINT fk_website FOREIGN KEY (website_id) REFERENCES store_website(website_id) ON DELETE CASCADE, CONSTRAINT UNIQUE (entity_id, customer_id, qty, from_date, to_date, website_id) );

Unique Constraint

You can have:

Same customer/product/qty with different website_id
Same customer/product/website with different qty
Exact duplicate including website_id (avoid)

Foreign Key Constraint

The foreign key ensures:

You can only use valid website IDs
If a website is deleted, associated prices are automatically removed
Data integrity is maintained

Price Resolution Logic

Resolution Order

When a customer views a product, the system:

Find all customer prices for entity_id + customer_id
Filter by website:
- Include prices where website_id = current website
- Include prices where website_id = 0 (global)
Filter by date (if from_date/to_date set)
Filter by quantity (qty <= cart quantity)
Select best price:
- Website-specific price (if exists)
- Global price (website_id=0) as fallback
- Highest qty tier that matches

Example Resolution

Setup:

Customer: 456 Product: 123 Current Website: 2 (EU) Cart Quantity: 15 Price Entries: Entry A: qty=1, price=$100, website_id=0 (global) Entry B: qty=10, price=$95, website_id=0 (global) Entry C: qty=1, price=€85, website_id=2 (EU) Entry D: qty=10, price=€80, website_id=2 (EU)

Resolution:

Filter by website: Keep Entry A, B, C, D (all match website 2 or 0)
Filter by quantity: Keep Entry A, C, D (qty 10 ≤ 15)
Select best price:
- Website-specific: Entry D (qty=10, €80, website_id=2)
- Entry D wins (website-specific + highest matching qty)

Result: €80.00 applied

Frontend Display

Product Page

Shows website-specific pricing:

Product: Widget ABC Your Price: €85.00 (Viewing EU Website)

Cart

Displays active website price:

Widget ABC Website: EU Website Quantity: 1 Unit Price: €85.00

Customer Account

"Download My Prices" CSV includes website info:

Product,SKU,Quantity,Price,Currency,Website Widget ABC,WGT-ABC,1,85.00,EUR,EU Website Widget ABC,WGT-ABC,1,100.00,USD,US Website Widget ABC,WGT-ABC,1,95.00,USD,All Websites

Best Practices

Website Configuration

Do:

Use website_id=0 for default/fallback pricing
Set website-specific prices only when needed
Document which websites use which currencies
Test customer experience on each website
Consider tax implications per region

Don't:

Create prices for non-existent website IDs
Forget to set global fallback prices
Mix currencies on the same website
Create unnecessary website-specific entries

Regional Strategy

Centralized Pricing

All pricing at website_id=0 (global)

Good for: Single-currency stores, simple setups

Hybrid Approach

Global defaults + regional overrides

Good for: Most multi-website scenarios

Fully Separated

Every website has its own price entries

Good for: Complex multi-currency, independent regions

Troubleshooting

Wrong Price on Website

Check:

Current website ID matches price entry
Global fallback (website_id=0) exists
Customer is logged in
Website filter in admin grid
Cache cleared after changes

Debug Query:

SELECT * FROM pricesystem_customerprice WHERE entity_id = 123 AND customer_id = 456 AND (website_id = 2 OR website_id = 0) ORDER BY website_id DESC, qty DESC;

Price Not Found on Website

Verify:

Price entry exists with correct website_id
Foreign key constraint hasn't deleted it
Website ID is valid in store_website table
Customer has access to that website

Duplicate Entry Error

Error Message:

Duplicate entry '123-456-1-NULL-NULL-2' for key 'UNIQUE'

Cause: Trying to create duplicate entry for same:

entity_id + customer_id + qty + dates + website_id

Solution:

Edit existing entry, or
Change website_id, or
Change qty, or
Change date range

Combining Features

Full Feature Combination Example

Multi-Website + Quantity Tiers + Time-Based: US Website Q1 Campaign: qty=1, price=$90, from=2025-01-01, to=2025-03-31, website_id=3 qty=50, price=$85, from=2025-01-01, to=2025-03-31, website_id=3 qty=100, price=$80, from=2025-01-01, to=2025-03-31, website_id=3 EU Website Q1 Campaign: qty=1, price=€80, from=2025-01-01, to=2025-03-31, website_id=2 qty=25, price=€75, from=2025-01-01, to=2025-03-31, website_id=2 qty=75, price=€70, from=2025-01-01, to=2025-03-31, website_id=2 Global Fallback (Year-Round): qty=1, price=$100, from=NULL, to=NULL, website_id=0 qty=50, price=$95, from=NULL, to=NULL, website_id=0

Reporting

Prices by Website Query

SELECT w.name as website, c.email as customer, p.sku, cp.qty, cp.value FROM pricesystem_customerprice cp JOIN store_website w ON cp.website_id = w.website_id JOIN customer_entity c ON cp.customer_id = c.entity_id JOIN catalog_product_entity p ON cp.entity_id = p.entity_id WHERE cp.website_id = 2 ORDER BY c.email, p.sku;

Global vs Website-Specific Count

SELECT CASE WHEN website_id = 0 THEN 'Global' ELSE 'Website-Specific' END as type, COUNT(*) as count FROM pricesystem_customerprice GROUP BY website_id = 0;

Coverage Analysis

-- Products with website-specific pricing SELECT p.sku, GROUP_CONCAT(DISTINCT w.name) as websites FROM pricesystem_customerprice cp JOIN catalog_product_entity p ON cp.entity_id = p.entity_id JOIN store_website w ON cp.website_id = w.website_id WHERE cp.website_id >0 GROUP BY p.sku;

Migration Scenarios

Converting Global to Website-Specific

Step 1: Export existing global prices

SELECT * FROM pricesystem_customerprice WHERE website_id = 0;

Step 2: Create website-specific copies

INSERT INTO pricesystem_customerprice (entity_id, customer_id, qty, value, from_date, to_date, website_id) SELECT entity_id, customer_id, qty, value, from_date, to_date, 2 as website_id FROM pricesystem_customerprice WHERE website_id = 0;

Step 3: Adjust website-specific values as needed

Step 4: Optionally remove global entries