Mon 26th Jan 2026

Designing a potentially bulletproof inventory schema for mysql

Database MySQL
Image for blog: Designing a potentially bulletproof inventory schema for mysql

In this article I get into how I designed an inventory schema for mySQL that can fit most inventory needs in addition to that, I further go into defining state and transactions as related to databases and further exploring the balance between state and transactions.

Backstory

I once needed to create an application that would be used for stock keeping. My first instinct(as every person who is green in this area) when building the inventory system database was to create a simple products table with a quantity column and that worked — until it didn’t.


The first crack came when I didn't have an auditable history to know what led to the quantity it had, this affects the reliability aspect of the data stored. Eg if the current quantity of a product was 9 I didn’t have a trail back to the stock being bought or sold to reach that quantity.


The next one was when I needed to store more data tied to a product like size, color, price and some other variations a product could have. I added some more columns to facilitate this and within a few days, I saw this wasn’t sustainable as the nuances would have kept on increasing - this led me to actually give some time and research on a good SQL schema to store the data.


Requirements Specification

Having seen the issue - I set some requirements for the new schema and they were the following:

  1. Store a product along with any variation combination it might have eg a product Cotton Shirt-Size L-Red - has a variation of size and color while Potato Crisps-100g-Vinegar has a variation of weight and flavor
  2. Be able to store a journal which acts as a source for “Transactions” of the inventory


After doing the short definition of what I wanted to achieve as described in the spec above, I set about reading on how to store inventory in a database. I came across two terms not constrained to an inventory database but just databases in general. These were state and transactions


State and Transactions

State simply means the current snapshot of the data at the time of fetching it.


Transactions refer to the operations that led to a particular state of the database data.


Quickly, I saw with the implementation I had - my emphasis was on the state and I had no backing transactions. However, simply switching to a transaction based system would not resolve all issues as I still had to store the possible variations. But simply reading on it I came to realise each had its own pros and cons which I share below:


Pros of State:

  1. Fast reads - it is great for UI ie dashboards
  2. Easy to cache and replicate


Cons of State:

  1. Not explaining ‘how’ the state is what it is which is crucial for audits
  2. Vulnerable to corruption if it is not backed by a ledger eg we might know the current quantity is 9 but is it 9 because the products were bought, sold or stolen.


Pros of Transactions:

  1. Full traceability - the history can be replayed to rebuild the current state
  2. Supports reconciliation, rollback and audit


Cons of Transactions:

  1. Slower queries - there’s a need to aggregate almost always to get the current state
  2. More complex logic to maintain consistency


The conclusion I arrived it is that for perfomance and UX state is to be considered while for integrity and ‘auditability’ transactions are essential and are a source of truth. However, a combination of both is almost always needed.


Getting Into The Work

On to the implementation now - I go into all the tables created: a visual represantation paired with the corresponding mySQL create table statement. Note that the statements are verbose for most of the samples below.


Product Table

This was a basic product table - consider it as a building block. In my case, I needed it to have a brand for user friendliness while in the UI.



The MySQL CREATE TABLE statement for it is:

CREATE TABLE products (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` VARCHAR(150) NOT NULL,
`brand` TEXT,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
);


Attributes

This table is also a building block but for attributes and will be a parent to any variation that might be needed just to mention a few: weight, size and color



Its corresponding MySQL CREATE TABLE implementation is:

CREATE TABLE attributes (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
);


Attribute Values

This table is used to store the values of a specific attirbute - an example would be for an attribute with a name of weight its values can be 50g , 100g or 2kg and so on



The MySQL implementation of this table is as below:

-- The attribute values
CREATE TABLE attribute_values (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`attribute_id` bigint(20) unsigned NOT NULL,
`value` VARCHAR(100) NOT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `attribute_id_foreign` (`attribute_id`),
CONSTRAINT `attribute_id_foreign` FOREIGN KEY (`attribute_id`) REFERENCES `attributes` (`id`),
);


Product variations

This table is the store for the variations of a product based off an SKU(stock keeping unit) - the SKU will be a unique combination of the attribute values which will be human readable(well at least for the ones involved with the inventory process). A benefit of this is that it can also store a product which has no variations, only that the SKU will just be an abbreviation of its name.



The corresponding MySQL implementation:

CREATE TABLE attribute_values (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`product_id` bigint(20) unsigned NOT NULL,
`sku` VARCHAR(100) NOT NULL,
`current_quantity`decimal(10,2) unsigned NOT NULL DEFAULT 0,
`minimum_threshold`decimal(10,2) unsigned NOT NULL,
`maximum_threshold`decimal(10,2) unsigned NOT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `sku_unique` (`sku`)
KEY `product_id_foreign` (`product_id`),
CONSTRAINT `prduct_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
);



Variation Attributes

This is table which will store all the attributes a product variation has. It has a many-to-many relationship with product_variations and attributes table



The relevant mySQL implementation is as below:

CREATE TABLE variation_attributes (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`variation_id` bigint(20) unsigned NOT NULL,
`attribute_id` bigint(20) unsigned NOT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `variation_id_foreign` (`variation_id`),
KEY `attribute_id_foreign` (`attribute_id`),
CONSTRAINT `variation_id_foreign` FOREIGN KEY (`variation_id`) REFERENCES `product_variations` (`id`),
CONSTRAINT `attribute_id_foreign` FOREIGN KEY (`attribute_id`) REFERENCES `attribute_values` (`id`),
);


Inventory journals

This table is used to store the movements of the inventory - an associated trigger to update quantities is used as well



Its supporting mySQL implementation is as follows (NB this has a trigger to update the 'state' of the product variation's current quantity:

CREATE TABLE inventory_journals (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`variation_id` bigint(20) unsigned NOT NULL,
`narration` varchar(50) NOT NULL,
`quantity`decimal(10,2) unsigned NOT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `variation_id_foreign` (`variation_id`),
CONSTRAINT `variation_id_foreign` FOREIGN KEY (`variation_id`) REFERENCES `product_variations` (`id`),
);

-- trigger to update the variant quantity
CREATE TRIGGER trg_after_inventory_journals_insert
AFTER INSERT ON inventory_journals
FOR EACH ROW
BEGIN
UPDATE product_variations
SET current_quantity = current_quantity + NEW.quantity
WHERE id = NEW.variation_id;
END


Testing

With the schema done, we can move to test it using an example scenario.


Say we have some potato crisps of two brands: Krispii and Tropical Heat. For Krispii there's 2 packets of 50g for a Chilli flavor and 3 packets of 100g for a chicken flavor. For Tropical Heat there's 3 packets of 150g ketchup flavor flavor and 1 packet of 200g of salty vinegar flavor.

## The base product
INSERT INTO products (name, brand) VALUES
('Potato Crisps', 'Krispii'), -- id of 1 assumed
('Potato Crisps', 'Tropical Heat') -- id of 2 assumed;

## The base attributes
INSERT INTO attributes (name) VALUES ('Size'), ('Flavor'); -- id of 1 & 2 assumed here as well

## attribute values
INSERT INTO attribute_values (attribute_id, value) VALUES
(1, '50g'), (1, '100g'), (1, '150g'), (1, '200g'), -- Sizes
(2, 'Chilli'), (2, 'Roast Chicken'), (2, 'Ketchup'), (2, 'Salty Vinegar'); -- Flavors

## variants for krispii
-- 50g chilli
INSERT INTO product_variations (product_id, sku) VALUES (1, 'KRI-CHIL-50');
INSERT INTO variation_attributes (variation_id, attribute_value_id) VALUES (1, 1), (1, 5);
INSERT INTO inventory_journals (variation_id, narration, quantity) VALUES (1 , 'New stock' , 2);
-- 100g chicken
INSERT INTO product_variations (product_id, sku) VALUES (1, 'KRI-CHIC-100');
INSERT INTO variation_attributes (variation_id, attribute_value_id) VALUES (2, 2), (2, 6);
INSERT INTO inventory_journals (variation_id, narration, quantity) VALUES (2 , 'New stock' , 3);

# variants for tropical heat
-- 150g ketchup
INSERT INTO product_variations (product_id, sku) VALUES (2, 'TRO-KETCH-150');
INSERT INTO variation_attributes (variation_id, attribute_value_id) VALUES (3, 3), (3, 7);
INSERT INTO inventory_journals (variation_id, narration, quantity) VALUES (3 , 'New stock' , 3);
-- 200g chicken
INSERT INTO product_variations (product_id, sku) VALUES (2, 'TRO-VIN-200');
INSERT INTO variation_attributes (variation_id, attribute_value_id) VALUES (4, 4), (4, 7);
INSERT INTO inventory_journals (variation_id, narration, quantity) VALUES (4 , 'New stock' , 1);


To further test a sample of this DB schema type you can check out a small playground I had made of it


Conclusion

With this schema I met my two requirements and is currently scalable from all I’ve been able to see.


Might you have a comment or something to add on - let me know below.

Share on Twitter

Any comment or feedback please share below!

Table Of Contents