バーコードデータのデータベース設計：スキーマパターン
Relational database schema patterns for barcode data — product tables, scan event logging, inventory tracking, and reporting queries.
Database Design for Barcode Data
Storing barcode-related data effectively requires understanding the structure of barcode numbers, the relationships between products and their identifiers, and the query patterns your applications will use. This guide covers schema design for barcode-centric systems.
GTIN Storage
The most fundamental question: how to store GTINs in a database.
Recommendation: Store GTINs as a 14-character string (CHAR(14)), zero-padded on the left. This normalizes GS1 Standards & Identifiers">gtin-8/" class="glossary-term-link" data-term="GTIN-8" data-definition="8-digit product identifier for space." data-category="1D Linear Symbologies">EAN-8 barcodes." data-category="GS1 Standards & Identifiers">GTIN-8, UPC-A barcodes in North America." data-category="GS1 Standards & Identifiers">GTIN-12, EAN-13, the global standard." data-category="GS1 Standards & Identifiers">GTIN-13, and ITF-14 barcodes on cases and pallets." data-category="GS1 Standards & Identifiers">GTIN-14 into a single comparable format.
EAN-8: 00000049999994
UPC-A: 00614141999996
EAN-13: 05901234123457
GTIN-14: 10614141999996
Why not integers? Leading zeros are significant in GTINs. Storing 0614141999996 as an integer drops the leading zero, causing lookup failures when a scanner returns the zero-padded form.
Core Schema
A minimal product-barcode schema:
| Table | Key Columns | Purpose |
|---|---|---|
products |
id, name, description | Product master data |
product_barcodes |
id, product_id, gtin, symbology | Links GTINs to products |
barcode_scans |
id, gtin, scanned_at, location_id, user_id | Scan event log |
locations |
id, name, gln | Physical locations (GLN-identified) |
Indexing Strategy
- Primary lookup: Unique index on
product_barcodes.gtinfor O(1) scan-to-product resolution - Scan history: Index on
barcode_scans(gtin, scanned_at)for time-range queries - Location queries: Index on
barcode_scans(location_id, scanned_at)for per-location activity
GS1 Application Identifier Data
For GS1-128 and Data Matrix with GS1 AIs for pharma and food traceability." data-category="2D & Matrix Symbologies">GS1 DataMatrix scans that contain structured AI data, you have two storage approaches:
Parsed columns: Extract each AI into a dedicated column (gtin, batch_number, expiry_date, serial_number). This enables direct SQL queries and indexing on individual fields.
JSON/structured field: Store the full AI data as a JSON document. This is flexible for varying AI combinations but harder to index and query.
Most systems use parsed columns for the common AIs (01, 10, 17, 21) and a supplementary JSON field for less common ones.
Historical Data
- Never delete barcode records; soft-delete or archive them
- GTINs should not be reused for at least 48 months (GS1 rule), so maintain history
- Track GTIN assignment dates and retirement dates
- Store the full scan string alongside parsed fields for audit purposes
Multi-Format Support
If your system handles multiple barcode types, include a symbology column to record which format was scanned. This helps diagnose scanning issues and track symbology migration (e.g., from Code 39 to Code 128).
Performance Considerations
- Scan volume: A busy warehouse generates millions of scan records daily. Partition the scans table by date
- Lookup latency: POS systems need sub-10ms GTIN lookups. Use in-memory caching (Redis) for the product-barcode mapping
- Batch operations: Import and export tools should handle bulk GTIN operations efficiently
- Data validation: Enforce check digit validation at the database level with a CHECK constraint or trigger