QR Igniter uses MySQL 8.x with a normalized schema designed for multi-tenant QR code management.
Entity Relationship
┌──────────┐ ┌──────────┐ ┌───────────┐ ┌──────────┐
│ Client │────▶│ Brand │────▶│ Campaign │────▶│ QR Code │
└──────────┘ └──────────┘ └───────────┘ └────┬─────┘
│ │
│ (logo) │
▼ ▼
┌──────────┐ ┌──────────┐
│ File │ │ Scan │
│ Storage │ └──────────┘
└──────────┘
Core Tables
clients
| Column |
Type |
Description |
| id | BIGINT UNSIGNED | Primary key |
| name | VARCHAR(255) | Client name |
| email | VARCHAR(255) | Contact email |
| phone | VARCHAR(50) | Contact phone |
| address | TEXT | Business address |
| status | ENUM | active, inactive |
| notes | TEXT | Internal notes |
| created_at | TIMESTAMP | Creation time |
| updated_at | TIMESTAMP | Last update |
brands
| Column |
Type |
Description |
| id | BIGINT UNSIGNED | Primary key |
| client_id | BIGINT UNSIGNED | FK to clients |
| name | VARCHAR(255) | Brand name |
| logo_path | VARCHAR(255) | Path to logo file |
| description | TEXT | Brand description |
| website | VARCHAR(255) | Brand website URL |
| status | ENUM | active, inactive |
| created_at | TIMESTAMP | Creation time |
| updated_at | TIMESTAMP | Last update |
campaigns
| Column |
Type |
Description |
| id | BIGINT UNSIGNED | Primary key |
| brand_id | BIGINT UNSIGNED | FK to brands |
| name | VARCHAR(255) | Campaign name |
| description | TEXT | Campaign details |
| start_date | DATE | Start date |
| end_date | DATE | End date (nullable) |
| status | ENUM | active, paused, completed |
| created_at | TIMESTAMP | Creation time |
| updated_at | TIMESTAMP | Last update |
qr_codes
| Column |
Type |
Description |
| id | BIGINT UNSIGNED | Primary key |
| campaign_id | BIGINT UNSIGNED | FK to campaigns |
| gtin | VARCHAR(14) | GS1 GTIN (AI 01) |
| batch_lot | VARCHAR(20) | Batch/Lot (AI 10) |
| serial_number | VARCHAR(20) | Serial (AI 21) |
| expiry_date | VARCHAR(6) | YYMMDD (AI 17) |
| cpv | VARCHAR(20) | CPV (AI 22) |
| destination_url | VARCHAR(2048) | Redirect URL |
| digital_link_uri | VARCHAR(2048) | Generated GS1 URI |
| use_interim_selector | BOOLEAN | Show selector page |
| embed_logo | BOOLEAN | Embed brand logo |
| status | ENUM | active, inactive |
| created_at | TIMESTAMP | Creation time |
| updated_at | TIMESTAMP | Last update |
scans
| Column |
Type |
Description |
| id | BIGINT UNSIGNED | Primary key |
| qr_code_id | BIGINT UNSIGNED | FK to qr_codes |
| ip_address | VARCHAR(45) | Client IP |
| user_agent | TEXT | Browser/device info |
| device_type | ENUM | mobile, tablet, desktop |
| country | VARCHAR(100) | Country name |
| city | VARCHAR(100) | City name |
| language | VARCHAR(10) | Accept-Language |
| referrer | VARCHAR(2048) | HTTP Referer |
| scanned_at | TIMESTAMP | Scan timestamp |
gtin_data
| Column |
Type |
Description |
| id | BIGINT UNSIGNED | Primary key |
| gtin | VARCHAR(14) | Product GTIN |
| product_name | VARCHAR(255) | Product name |
| brand_name | VARCHAR(255) | Brand name |
| description | TEXT | Product description |
| image_url | VARCHAR(2048) | Product image |
| category | VARCHAR(255) | Product category |
| metadata | JSON | Additional data |
| created_at | TIMESTAMP | Creation time |
| updated_at | TIMESTAMP | Last update |
Indexes
-- QR Code lookups
INDEX idx_qr_codes_gtin (gtin)
INDEX idx_qr_codes_digital_link (digital_link_uri)
INDEX idx_qr_codes_campaign (campaign_id)
-- Scan analytics
INDEX idx_scans_qr_code (qr_code_id)
INDEX idx_scans_timestamp (scanned_at)
INDEX idx_scans_country (country)
-- GTIN lookups
UNIQUE INDEX idx_gtin_data_gtin (gtin)
Relationships
clients 1 ────── * brands
brands 1 ────── * campaigns
campaigns 1 ────── * qr_codes
qr_codes 1 ────── * scans
Next Steps