Database Schema

Purpose and Scope

This document provides a comprehensive reference for the database schema of the GP247/Shop e-commerce package. It documents the structure of all tables created by the migration file, including column definitions, data types, indexes, foreign key relationships, and multi-store isolation mechanisms. This schema supports complex e-commerce operations including product variants, order management, customer accounts, and multi-tenant store architecture.

For information about how configuration data is stored and managed, see Configuration-Driven System Behavior. For details on multi-store query filtering logic, see Multi-Store and Multi-Vendor Architecture.

Schema Overview

The database schema consists of 30+ tables organized into five logical domains. Tables use UUID primary keys for most entities (except reference data which uses auto-increment integers). The schema supports internationalization through separate description tables that use composite keys of entity_id and lang.

Domain Organization

Sources: src/DB/migrations/00_00_00_create_tables_shop.php1-506

Catalog Domain Tables

shop_product

The core product entity table supporting three product types via the kind column: single products (0), bundle products (1), and group products (2).

Column	Type	Default	Description
id	UUID	-	Primary key
sku	VARCHAR(50)	-	Stock keeping unit (indexed)
upc, ean, jan, isbn, mpn	VARCHAR	NULL	Product identification codes
image	VARCHAR(255)	NULL	Primary product image path
brand_id	UUID	0	Foreign key to shop_brand (indexed)
supplier_id	UUID	0	Foreign key to shop_supplier (indexed)
price	DECIMAL(15,2)	0	Base selling price
cost	DECIMAL(15,2)	0	Cost of goods sold
stock	INTEGER	0	Available inventory quantity
sold	INTEGER	0	Total units sold
minimum	INTEGER	0	Minimum order quantity
weight_class, weight	VARCHAR/DECIMAL	NULL/0	Weight for shipping
length_class, length, width, height	VARCHAR/DECIMAL	NULL/0	Dimensions for shipping
kind	TINYINT	0	Product type: 0=single, 1=bundle, 2=group (indexed)
tag	VARCHAR(50)	'physical'	Product tag classification (indexed)
tax_id	VARCHAR(50)	0	Tax rule: 0=no tax, 'auto'=default tax (indexed)
status	TINYINT	0	Visibility status (indexed)
approve	TINYINT	1	Approval status for vendors (indexed)
sort	INTEGER	0	Display sort order
view	INTEGER	0	View counter
alias	VARCHAR(120)	-	URL-friendly slug (indexed)
date_lastview	TIMESTAMP	NULL	Last viewed timestamp
date_available	DATE	NULL	Product availability date
created_at, updated_at	TIMESTAMP	-	Laravel timestamps

Note: Products do not have a direct store_id column. Multi-store associations use the junction table shop_product_store.

Sources: src/DB/migrations/00_00_00_create_tables_shop.php186-221

shop_product_description

Internationalized product content using composite primary key of product_id and lang.

Column	Type	Description
product_id	UUID	Foreign key to shop_product
lang	VARCHAR(10)	Language code (indexed)
name	VARCHAR(255)	Localized product name
keyword	VARCHAR(200)	SEO keywords
description	VARCHAR(500)	Short description
content	MEDIUMTEXT	Full product description HTML

Unique constraint: (product_id, lang)

Sources: src/DB/migrations/00_00_00_create_tables_shop.php223-234

shop_product_image

Product image gallery supporting multiple images per product.

Column	Type	Description
id	UUID	Primary key
image	VARCHAR(255)	Image file path
product_id	UUID	Foreign key to shop_product (indexed)

Sources: src/DB/migrations/00_00_00_create_tables_shop.php236-243

shop_product_build

Bundle product composition table. When a product has kind=1 (bundle), this table defines which component products are included and their quantities.

Column	Type	Description
build_id	UUID	Foreign key to parent bundle product
product_id	UUID	Foreign key to component product
quantity	INTEGER	Quantity of component in bundle

Composite primary key: (build_id, product_id)

Sources: src/DB/migrations/00_00_00_create_tables_shop.php245-253

shop_product_group

Product variant grouping table. When a product has kind=2 (group), this table links related product variants (e.g., different sizes/colors of same item).

Column	Type	Description
group_id	UUID	Group identifier
product_id	UUID	Foreign key to variant product

Composite primary key: (group_id, product_id)

Sources: src/DB/migrations/00_00_00_create_tables_shop.php255-262

shop_product_attribute

Configurable product options (e.g., size, color) that can add to the base price. Each attribute belongs to an attribute group which defines the input type (radio, select, checkbox).

Column	Type	Default	Description
id	BIGINT	-	Auto-increment primary key
name	VARCHAR(255)	-	Attribute option name
attribute_group_id	INTEGER	-	Foreign key to shop_attribute_group
product_id	UUID	-	Foreign key to shop_product
add_price	DECIMAL(15,2)	0	Price adjustment for this option
sort	INTEGER	0	Display order
status	TINYINT	1	Active status

Composite index: (product_id, attribute_group_id)

Sources: src/DB/migrations/00_00_00_create_tables_shop.php285-297

shop_attribute_group

Defines types of product attributes and their input controls.

Column	Type	Default	Description
id	INTEGER	-	Auto-increment primary key
name	VARCHAR(255)	-	Group name (e.g., "Size", "Color")
status	TINYINT	0	Active status
sort	INTEGER	0	Display order
type	VARCHAR(50)	-	Input type: radio, select, checkbox
created_at, updated_at	TIMESTAMP	-	Laravel timestamps

Sources: src/DB/migrations/00_00_00_create_tables_shop.php273-283

shop_product_promotion

Time-limited price promotions for products.

Column	Type	Default	Description
product_id	UUID	-	Primary key, foreign key to shop_product
price_promotion	DECIMAL(15,2)	-	Promotional price
date_start	TIMESTAMP	NULL	Promotion start date
date_end	TIMESTAMP	NULL	Promotion end date
status_promotion	INTEGER	1	Active status
created_at, updated_at	TIMESTAMP	-	Laravel timestamps

Sources: src/DB/migrations/00_00_00_create_tables_shop.php397-407

shop_product_download

Digital download files associated with products (for digital goods).

Column	Type	Description
id	UUID	Primary key
product_id	UUID	Foreign key to shop_product
path	VARCHAR(255)	File path or URL

Sources: src/DB/migrations/00_00_00_create_tables_shop.php409-416

shop_category

Hierarchical category tree supporting unlimited nesting via self-referential parent column.

Column	Type	Default	Description
id	UUID	-	Primary key
image	VARCHAR(255)	NULL	Category image
alias	VARCHAR(120)	-	URL-friendly slug (indexed)
parent	UUID	NULL	Parent category ID (NULL for root)
top	INTEGER	0	Display in top menu flag
status	TINYINT	0	Visibility status
sort	INTEGER	0	Display order
created_at, updated_at	TIMESTAMP	-	Laravel timestamps

Note: Categories do not have a direct store_id column. Multi-store associations use the junction table shop_category_store.

Sources: src/DB/migrations/00_00_00_create_tables_shop.php34-46

shop_category_description

Internationalized category content.

Column	Type	Description
category_id	UUID	Foreign key to shop_category
lang	VARCHAR(10)	Language code (indexed)
title	VARCHAR(255)	Localized category name
keyword	VARCHAR(200)	SEO keywords
description	VARCHAR(500)	Category description

Unique constraint: (category_id, lang)

Sources: src/DB/migrations/00_00_00_create_tables_shop.php48-58

shop_brand

Product manufacturer/brand information.

Column	Type	Default	Description
id	UUID	-	Primary key
name	VARCHAR(255)	-	Brand name
alias	VARCHAR(120)	-	URL-friendly slug (indexed)
image	VARCHAR(255)	NULL	Brand logo
url	VARCHAR(100)	NULL	Brand website
status	TINYINT	0	Active status
sort	INTEGER	0	Display order
created_at, updated_at	TIMESTAMP	-	Laravel timestamps

Sources: src/DB/migrations/00_00_00_create_tables_shop.php20-32

shop_supplier

Vendor/supplier information for multi-vendor marketplace functionality.

Column	Type	Default	Description
id	UUID	-	Primary key
name	VARCHAR(255)	-	Supplier name
alias	VARCHAR(120)	-	URL-friendly slug (indexed)
email	VARCHAR(150)	NULL	Contact email
phone	VARCHAR(20)	NULL	Contact phone
image	VARCHAR(255)	NULL	Supplier logo
address	VARCHAR(100)	NULL	Physical address
url	VARCHAR(100)	NULL	Supplier website
status	TINYINT	1	Active status
store_id	UUID	1	Store isolation (indexed)
sort	INTEGER	0	Display order
created_at, updated_at	TIMESTAMP	-	Laravel timestamps

Note: This table has direct store_id column for multi-store isolation.

Sources: src/DB/migrations/00_00_00_create_tables_shop.php370-386

Transaction Domain Tables

shop_order

Main order record containing customer information, shipping address, and financial totals.

Key Financial Fields:

• subtotal: Sum of line item prices before adjustments
• discount: Total discount amount applied
• shipping: Shipping cost
• tax: Total tax amount
• other_fee: Additional fees (e.g., payment processing)
• total: Final order total (subtotal + shipping + tax + other_fee - discount)
• received: Amount paid by customer
• balance: Outstanding balance (total - received)

Multi-Currency Support:

• currency: Three-letter currency code (e.g., "USD", "EUR")
• exchange_rate: Exchange rate at time of order

Status Fields:

• status: Overall order status (references shop_order_status)
• payment_status: Payment state (references shop_payment_status)
• shipping_status: Shipment state (references shop_shipping_status)

Device Tracking:

• device_type: Device classification (indexed for analytics)
• user_agent: Browser user agent string
• ip: Customer IP address

Multi-Store Isolation:

• store_id: Store identifier (indexed, default 1)

Sources: src/DB/migrations/00_00_00_create_tables_shop.php77-118

shop_order_detail

Individual line items in an order. Each row represents one product purchased.

Column	Type	Default	Description
id	UUID	-	Primary key
order_id	UUID	-	Foreign key to shop_order
product_id	UUID	-	Foreign key to shop_product
name	VARCHAR(255)	-	Product name snapshot
price	DECIMAL(15,2)	0	Unit price at time of order
qty	INTEGER	0	Quantity ordered
store_id	UUID	1	Store identifier for multi-vendor
total_price	DECIMAL(15,2)	0	Line total (price × qty)
tax	DECIMAL(15,2)	0	Tax amount for this line
sku	VARCHAR(50)	-	SKU snapshot
currency	VARCHAR(10)	-	Currency code
exchange_rate	FLOAT	NULL	Exchange rate snapshot
attribute	VARCHAR(100)	NULL	Selected product attributes (JSON)
created_at, updated_at	TIMESTAMP	-	Laravel timestamps

Note: Product name and SKU are denormalized to preserve historical data even if product is later modified or deleted.

Sources: src/DB/migrations/00_00_00_create_tables_shop.php120-138

shop_order_history

Audit trail for order status changes and administrative notes.

Column	Type	Default	Description
id	UUID	-	Primary key
order_id	UUID	-	Foreign key to shop_order
content	VARCHAR(300)	-	History entry description
admin_id	UUID	0	Admin user who made the change
customer_id	UUID	0	Customer who made the change
order_status_id	INTEGER	0	Order status after change
add_date	TIMESTAMP	-	Timestamp of entry

Sources: src/DB/migrations/00_00_00_create_tables_shop.php140-151

shop_order_total

Financial breakdown of order total including subtotal, shipping, tax, discounts, and other fees. Each row represents one component of the order total.

Column	Type	Default	Description
id	UUID	-	Primary key
order_id	UUID	-	Foreign key to shop_order (indexed)
title	VARCHAR(255)	-	Line item description
code	VARCHAR(100)	-	Component code (e.g., "subtotal", "shipping")
value	DECIMAL(15,2)	0	Amount
text	VARCHAR(200)	NULL	Formatted display text
sort	INTEGER	1	Display order
created_at, updated_at	TIMESTAMP	-	Laravel timestamps

Sources: src/DB/migrations/00_00_00_create_tables_shop.php162-174

shop_shoppingcart

Persistent shopping cart storage. Supports multiple cart instances (default cart, wishlist, compare).

Column	Type	Default	Description
identifier	VARCHAR(100)	-	Session ID or customer ID
instance	VARCHAR(100)	-	Cart type (default, wishlist, compare)
content	TEXT	-	Serialized cart data
store_id	UUID	1	Store identifier (indexed)
created_at, updated_at	TIMESTAMP	-	Laravel timestamps

Composite index: (identifier, instance)

Sources: src/DB/migrations/00_00_00_create_tables_shop.php308-318

Customer Domain Tables

shop_customer

Customer account information. Supports optional fields based on configuration settings (see Configuration-Driven System Behavior).

Column	Type	Default	Description
id	UUID	-	Primary key
first_name	VARCHAR(100)	-	Required first name
last_name	VARCHAR(100)	NULL	Optional last name
first_name_kana	VARCHAR(100)	NULL	Japanese phonetic first name
last_name_kana	VARCHAR(100)	NULL	Japanese phonetic last name
email	VARCHAR(150)	NULL	Unique email address
sex	TINYINT	NULL	Gender: 0=women, 1=men
birthday	DATE	NULL	Date of birth
password	VARCHAR(100)	NULL	Hashed password
address_id	UUID	0	Default address (indexed)
postcode	VARCHAR(10)	NULL	Postal code
address1	VARCHAR(100)	NULL	Address line 1
address2	VARCHAR(100)	NULL	Address line 2
address3	VARCHAR(100)	NULL	Address line 3
company	VARCHAR(100)	NULL	Company name
country	VARCHAR(10)	VN	Country code
phone	VARCHAR(20)	NULL	Phone number
store_id	UUID	1	Store identifier (indexed)
remember_token	VARCHAR(100)	NULL	Laravel remember token
status	TINYINT	1	Account status (active/inactive)
group	TINYINT	1	Customer group
email_verified_at	TIMESTAMP	NULL	Email verification timestamp
created_at, updated_at	TIMESTAMP	-	Laravel timestamps

Unique constraint: email

Multi-Store Isolation: Direct store_id column isolates customers by store.

Sources: src/DB/migrations/00_00_00_create_tables_shop.php320-348

shop_customer_address

Multiple shipping/billing addresses per customer. Customers can maintain an address book of saved addresses.

Column	Type	Default	Description
id	UUID	-	Primary key
customer_id	UUID	-	Foreign key to shop_customer (indexed)
first_name	VARCHAR(100)	-	Recipient first name
last_name	VARCHAR(100)	NULL	Recipient last name
first_name_kana	VARCHAR(100)	NULL	Japanese phonetic first name
last_name_kana	VARCHAR(100)	NULL	Japanese phonetic last name
postcode	VARCHAR(10)	NULL	Postal code
address1	VARCHAR(100)	NULL	Address line 1
address2	VARCHAR(100)	NULL	Address line 2
address3	VARCHAR(100)	NULL	Address line 3
country	VARCHAR(10)	VN	Country code
phone	VARCHAR(20)	NULL	Phone number
created_at, updated_at	TIMESTAMP	-	Laravel timestamps

Sources: src/DB/migrations/00_00_00_create_tables_shop.php350-367 src/Admin/Models/AdminCustomer.php82-95

Reference Domain Tables

Reference tables store lookup data and workflow states. These tables use auto-increment integer primary keys rather than UUIDs.

shop_currency

Multi-currency support with exchange rates and formatting options.

Column	Type	Default	Description
id	INTEGER	-	Auto-increment primary key
name	VARCHAR(255)	-	Currency name (e.g., "US Dollar")
code	VARCHAR(10)	-	ISO currency code (unique)
symbol	VARCHAR(10)	-	Currency symbol (e.g., "$")
exchange_rate	FLOAT	-	Exchange rate to base currency
precision	TINYINT	2	Decimal places for display
symbol_first	TINYINT	0	Symbol position: 0=after, 1=before
thousands	VARCHAR	','	Thousands separator
status	TINYINT	0	Active status
sort	INTEGER	0	Display order
created_at, updated_at	TIMESTAMP	-	Laravel timestamps

Unique constraint: code

Sources: src/DB/migrations/00_00_00_create_tables_shop.php60-75

shop_order_status

Order workflow states (e.g., Pending, Processing, Completed, Cancelled).

Column	Type	Description
id	INTEGER	Auto-increment primary key
name	VARCHAR(255)	Status name
created_at, updated_at	TIMESTAMP	Laravel timestamps

Sources: src/DB/migrations/00_00_00_create_tables_shop.php153-160

shop_payment_status

Payment states (e.g., Unpaid, Partial, Paid, Refunded).

Column	Type	Description
id	INTEGER	Auto-increment primary key
name	VARCHAR(255)	Status name
created_at, updated_at	TIMESTAMP	Laravel timestamps

Sources: src/DB/migrations/00_00_00_create_tables_shop.php176-183

shop_shipping_status

Shipping states (e.g., Pending, Shipped, Delivered).

Column	Type	Description
id	INTEGER	Auto-increment primary key
name	VARCHAR(255)	Status name
created_at, updated_at	TIMESTAMP	Laravel timestamps

Sources: src/DB/migrations/00_00_00_create_tables_shop.php299-306

shop_tax

Tax rule definitions with percentage values.

Column	Type	Default	Description
id	INTEGER	-	Auto-increment primary key
name	VARCHAR(255)	-	Tax rule name (e.g., "VAT 20%")
value	INTEGER	0	Tax percentage (stored as integer, e.g., 20 for 20%)
created_at, updated_at	TIMESTAMP	-	Laravel timestamps

Sources: src/DB/migrations/00_00_00_create_tables_shop.php388-396

Multi-Store Junction Tables

Junction tables enable many-to-many relationships and multi-store catalog sharing. These tables use composite primary keys.

shop_product_category

Many-to-many relationship between products and categories. A product can belong to multiple categories.

Column	Type	Description
product_id	UUID	Foreign key to shop_product
category_id	UUID	Foreign key to shop_category

Composite primary key: (product_id, category_id)

Sources: src/DB/migrations/00_00_00_create_tables_shop.php264-271

shop_product_store

Multi-store catalog sharing. Links products to specific stores, allowing catalog visibility control per store.

Column	Type	Description
product_id	UUID	Foreign key to shop_product
store_id	UUID	Store identifier

Composite primary key: (product_id, store_id)

Note: Query builders automatically join this table when multi-store mode is enabled.

Sources: src/DB/migrations/00_00_00_create_tables_shop.php418-425

shop_category_store

Multi-store catalog sharing for categories.

Column	Type	Description
category_id	UUID	Foreign key to shop_category
store_id	UUID	Store identifier

Composite primary key: (category_id, store_id)

Sources: src/DB/migrations/00_00_00_create_tables_shop.php427-434

Support Tables

shop_sessions

Custom session storage table for shop-specific sessions.

Column	Type	Description
id	VARCHAR(100)	Session ID (unique)
customer_id	UUID	Logged-in customer ID (nullable)
ip_address	VARCHAR(45)	Client IP address
user_agent	TEXT	Browser user agent
payload	TEXT	Serialized session data
last_activity	INTEGER	Unix timestamp of last activity
created_at, updated_at	TIMESTAMP	Laravel timestamps

Unique constraint: id

Sources: src/DB/migrations/00_00_00_create_tables_shop.php437-448

shop_password_resets

Temporary token storage for password reset requests.

Column	Type	Description
email	VARCHAR(150)	Customer email (indexed)
token	VARCHAR(255)	Reset token hash
created_at	TIMESTAMP	Token creation time

Index: email

Sources: src/DB/migrations/00_00_00_create_tables_shop.php450-458

Entity Relationships

The following diagram illustrates the primary foreign key relationships between entities:

Sources: src/DB/migrations/00_00_00_create_tables_shop.php1-506

Indexing Strategy

The schema implements a comprehensive indexing strategy to optimize query performance:

Primary Indexes

• UUID Primary Keys: All major entities use UUID primary keys for global uniqueness and distributed system compatibility
• Integer Primary Keys: Reference tables use auto-increment integers for efficiency
• Composite Primary Keys: Junction tables use composite keys on both foreign key columns

Secondary Indexes

Performance Optimization:

• alias indexes on products and categories support SEO-friendly URL lookups
• store_id indexes on orders, customers, suppliers, and carts enable efficient multi-store filtering
• kind, tag, status, approve indexes on products support catalog filtering
• Composite index (identifier, instance) on shopping cart supports session-based cart retrieval
• lang indexes on description tables optimize localization queries

Sources: src/DB/migrations/00_00_00_create_tables_shop.php1-506

Multi-Store Column Patterns

The schema implements a hybrid multi-tenant architecture with two distinct patterns:

Direct Store ID Columns

Tables with direct store_id columns for hard data isolation:

Table	Column	Default	Isolation Type
shop_order	store_id	1	Hard isolation (indexed)
shop_order_detail	store_id	1	Vendor tracking
shop_customer	store_id	1	Hard isolation (indexed)
shop_supplier	store_id	1	Hard isolation (indexed)
shop_shoppingcart	store_id	1	Session isolation (indexed)

Query Pattern: Models use where('store_id', session('adminStoreId')) to filter results.

Example from AdminCustomer:

Junction Table Store Associations

Catalog entities use junction tables for flexible multi-store visibility:

Table	Junction Table	Purpose
shop_product	shop_product_store	Product visibility per store
shop_category	shop_category_store	Category visibility per store

Query Pattern: Models join junction tables and filter by store_id when multi-store mode is enabled.

Sources: src/DB/migrations/00_00_00_create_tables_shop.php77-506 src/Admin/Models/AdminCustomer.php20-26

Internationalization Schema Pattern

The system uses separate description tables for internationalized content, following this pattern:

1. Base Entity Table: Contains language-independent data (IDs, numbers, dates, flags)
2. Description Table: Contains translatable text with composite key (entity_id, lang)

Example: Product Internationalization

Tables Using This Pattern:

• shop_product + shop_product_description
• shop_category + shop_category_description

Query Pattern: Models join description tables filtered by lang parameter to retrieve localized content. See Frontend Request Flow for implementation details.

Sources: src/DB/migrations/00_00_00_create_tables_shop.php223-234 src/DB/migrations/00_00_00_create_tables_shop.php48-58

Migration Execution

The migration file is located at src/DB/migrations/00_00_00_create_tables_shop.php and follows Laravel migration conventions:

Connection Configuration:

• Uses dynamic connection: Schema::connection(GP247_DB_CONNECTION)
• Uses table prefix: GP247_DB_PREFIX prepended to all table names

Execution Order:

• File naming convention 00_00_00_ ensures this migration runs before other shop migrations
• The up() method creates all tables
• The down() method drops all tables in safe order

Idempotency:

• Migration calls $this->down() before creating tables to ensure clean state
• Safe to re-run during development

Sources: src/DB/migrations/00_00_00_create_tables_shop.php14-19 src/DB/migrations/00_00_00_create_tables_shop.php468-505