Inventory Database
An inventory database is the foundation of any asset tracking system. Whether you build custom or buy software, understanding database concepts helps you make better decisions and get more from your system.
Build vs Buy: The Real Costs
Building Custom
| Cost Category | Year 1 | Years 2-5 (per year) |
|---|---|---|
| Development | $20,000-100,000 | - |
| Hosting | $1,200-6,000 | $1,200-6,000 |
| Maintenance | $5,000-20,000 | $10,000-30,000 |
| Bug fixes | Included | $2,000-10,000 |
| Feature additions | - | $5,000-20,000 |
| Total | $26,000-126,000 | $18,000-66,000 |
Buying Software
| Cost Category | Year 1 | Years 2-5 (per year) |
|---|---|---|
| Subscription | $1,200-12,000 | $1,200-12,000 |
| Implementation | $0-5,000 | - |
| Training | $0-2,000 | - |
| Total | $1,200-19,000 | $1,200-12,000 |
5-year TCO comparison:
- Custom build: $98,000-390,000
- SaaS software: $6,000-67,000
Build only if you have truly unique requirements that no software addresses.
When to Build Custom
Custom databases make sense when:
- Unique workflow: Your process doesn't fit any existing software
- Integration critical: Deep integration with proprietary systems
- Scale extreme: Millions of transactions daily
- Compliance specific: Regulatory requirements demand custom
- Developer resources: In-house team with capacity
When to Buy Software
Buy off-the-shelf when:
- Standard needs: Basic inventory tracking (most businesses)
- Limited IT: No dedicated development resources
- Speed matters: Need to be operational in weeks, not months
- Budget constrained: Can't justify development costs
- Best practices: Want proven workflows built-in
Database Design Fundamentals
If you're building custom, here's how to structure your inventory database:
Core Tables
-- Items (what you're tracking)
CREATE TABLE items (
id SERIAL PRIMARY KEY,
sku VARCHAR(50) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL,
description TEXT,
category_id INTEGER REFERENCES categories(id),
unit_cost DECIMAL(10,2),
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
-- Locations (where items can be)
CREATE TABLE locations (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
type VARCHAR(50), -- warehouse, vehicle, customer, etc.
address TEXT,
latitude DECIMAL(10,8),
longitude DECIMAL(11,8),
parent_id INTEGER REFERENCES locations(id)
);
-- Inventory (item quantities by location)
CREATE TABLE inventory (
id SERIAL PRIMARY KEY,
item_id INTEGER REFERENCES items(id),
location_id INTEGER REFERENCES locations(id),
quantity INTEGER DEFAULT 0,
reserved_quantity INTEGER DEFAULT 0,
last_counted TIMESTAMP,
UNIQUE(item_id, location_id)
);
-- Transactions (movement history)
CREATE TABLE transactions (
id SERIAL PRIMARY KEY,
item_id INTEGER REFERENCES items(id),
from_location_id INTEGER REFERENCES locations(id),
to_location_id INTEGER REFERENCES locations(id),
quantity INTEGER NOT NULL,
transaction_type VARCHAR(50), -- receive, transfer, ship, adjust
reference_number VARCHAR(100),
user_id INTEGER REFERENCES users(id),
notes TEXT,
created_at TIMESTAMP DEFAULT NOW()
);Supporting Tables
-- Categories
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
parent_id INTEGER REFERENCES categories(id)
);
-- Users
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(100),
role VARCHAR(50)
);
-- Tracking devices (GPS, RFID, etc.)
CREATE TABLE tracking_devices (
id SERIAL PRIMARY KEY,
device_identifier VARCHAR(100) UNIQUE,
device_type VARCHAR(50),
item_id INTEGER REFERENCES items(id),
last_seen_at TIMESTAMP,
last_latitude DECIMAL(10,8),
last_longitude DECIMAL(11,8)
);Common Queries
Current Inventory by Location
SELECT
l.name as location,
i.sku,
i.name as item_name,
inv.quantity,
inv.reserved_quantity,
(inv.quantity - inv.reserved_quantity) as available
FROM inventory inv
JOIN items i ON inv.item_id = i.id
JOIN locations l ON inv.location_id = l.id
WHERE inv.quantity > 0
ORDER BY l.name, i.name;Item Movement History
SELECT
t.created_at,
i.name as item,
fl.name as from_location,
tl.name as to_location,
t.quantity,
t.transaction_type,
u.name as performed_by
FROM transactions t
JOIN items i ON t.item_id = i.id
LEFT JOIN locations fl ON t.from_location_id = fl.id
LEFT JOIN locations tl ON t.to_location_id = tl.id
JOIN users u ON t.user_id = u.id
WHERE i.sku = 'ASSET-001'
ORDER BY t.created_at DESC;Low Stock Alert
SELECT
i.sku,
i.name,
SUM(inv.quantity) as total_quantity,
i.reorder_point
FROM items i
JOIN inventory inv ON i.id = inv.item_id
GROUP BY i.id, i.sku, i.name, i.reorder_point
HAVING SUM(inv.quantity) <= i.reorder_point;Database Selection
PostgreSQL
Best for: Most custom inventory systems
- Robust and reliable
- Excellent SQL compliance
- Good JSON support
- Strong community
- Free and open source
MySQL
Best for: Simpler requirements, WordPress integration
- Fast reads
- Wide hosting support
- Simple setup
- Good for web applications
SQLite
Best for: Single-user, local applications
- No server required
- File-based
- Good for prototypes
- Limited concurrency
MongoDB
Best for: Highly variable item attributes
- Flexible schema
- Good for varying product attributes
- Document-based
- Requires different design approach
Integrating Tracking Hardware
GPS Tracker Integration
Store location updates from GPS devices:
-- Receive GPS update
INSERT INTO location_updates (
tracking_device_id,
latitude,
longitude,
accuracy,
battery_level,
recorded_at
) VALUES ($1, $2, $3, $4, $5, NOW());
-- Update device last known location
UPDATE tracking_devices
SET
last_latitude = $2,
last_longitude = $3,
last_seen_at = NOW()
WHERE id = $1;RFID Integration
Record RFID scans:
-- Record RFID scan at checkpoint
INSERT INTO rfid_scans (
rfid_tag,
reader_id,
location_id,
scan_time
) VALUES ($1, $2, $3, NOW());
-- Update item location based on scan
UPDATE inventory
SET location_id = $3
WHERE item_id = (
SELECT item_id FROM tracking_devices WHERE device_identifier = $1
);Performance Considerations
Indexing
Essential indexes for inventory databases:
-- Item lookups
CREATE INDEX idx_items_sku ON items(sku);
CREATE INDEX idx_items_category ON items(category_id);
-- Inventory queries
CREATE INDEX idx_inventory_item ON inventory(item_id);
CREATE INDEX idx_inventory_location ON inventory(location_id);
-- Transaction history
CREATE INDEX idx_transactions_item ON transactions(item_id);
CREATE INDEX idx_transactions_date ON transactions(created_at);
CREATE INDEX idx_transactions_type ON transactions(transaction_type);Partitioning
For large transaction tables:
-- Partition transactions by month
CREATE TABLE transactions_2025_01 PARTITION OF transactions
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');The Easier Path: SaaS Software
Unless you have specific requirements demanding custom development, SaaS inventory software provides:
- Immediate availability: Start using today
- Proven design: Database decisions already made
- Ongoing updates: Features without development cost
- Support included: Help when things go wrong
- Integrations built-in: Connect to common systems
- Mobile access: Apps without building them
AirPinpoint provides inventory tracking without database decisions—just add trackers and go.
Making the Decision
Choose Custom Database If:
- Unique workflows that no software handles
- Integration with proprietary systems is critical
- You have developer resources for ongoing maintenance
- Compliance requires custom data handling
- Scale demands custom optimization
Choose SaaS Software If:
- Standard inventory tracking needs
- Limited technical resources
- Need to be operational quickly
- Want proven best practices
- Prefer predictable costs
For most businesses, the database question is answered by choosing the right software—let someone else manage the database while you focus on your business.
Our Solution
