A diamond is not a SKU

Most inventory systems rest on an assumption so ordinary it is never stated: that two units of the same product are interchangeable. Stock is a count. Sell one, decrement it.

A polished diamond breaks that assumption completely. Every stone is individually graded across around nine attributes — carat, colour, clarity, cut, shape, polish, symmetry, fluorescence, measurements — and the combination is what a buyer is purchasing. There is no SKU because there is no repeat. Inventory holds individuals, and quantity is always one.

Almost every downstream design decision follows from that.

Search is the primary interface, not a feature

Nobody asks for a stone by identifier. They describe one:

1.01 to 1.10 carat, F to G colour, VS1 to VS2 clarity, excellent cut, no fluorescence.

That is a range query across several dimensions at once, most of them ordered categoricals rather than numbers — and the ordering is domain knowledge, not something a database infers. G is adjacent to F and two steps from H. VS2 sits between VS1 and SI1. A system that stores these as strings can filter for exact matches and cannot answer the question anyone is actually asking.

The consequence: the grading scales have to be modelled as ordered scales with their positions explicit. Get that wrong at the start and every range search, every sort and every near-match suggestion is subtly incorrect in a way that looks like a preference rather than a bug.

Two stones with identical grades are not the same stone

Even matched on every graded attribute, buyers do not treat two stones as substitutes. Measurements differ, so one faces up larger. The grading laboratory differs, and the market does not weigh all laboratories equally. One has a certificate on file and the other is awaiting one.

So a system cannot silently substitute, cannot aggregate identical grades into a line with a count, and cannot treat the grading report as merely an attachment. The report number is the closest thing the domain has to a natural key — with the caveat that stones are occasionally re-graded, and a re-cut stone is a genuinely new stone that must not inherit the old identity.

Availability is not a boolean

A stone can be in the safe, with a customer on approval, consigned to a partner, at a laboratory for grading, or in transit. In several of those states it is still owned, still listed, and still sellable — subject to recall.

Which makes availability a position rather than a flag, and makes location history part of the record rather than an audit nicety. The question where is this stone and who has it is asked daily and has commercial consequences, so item-level activity history is a primary feature.

The same stone also legitimately appears on several lists at once, at different prices, offered to different buyers. Modelling price as a column on the item cannot express that.

Pricing is a calculation over a moving reference

Prices are derived from a market reference for the attribute combination, adjusted by discounts that shift with demand. The reference moves.

So a stored price is a snapshot with a date attached, not a property of the stone, and an offer made last week was made against last week's reference. Systems that store one price per item lose the ability to answer why a stone was offered at what it was — which matters, because somebody eventually asks.

Spreadsheets are the industry's real API

Trade lists move as spreadsheets. That is the interchange format, it is not going away, and treating it as a legacy inconvenience is a mistake.

The engineering reality is that import has to be forgiving about column naming and unit conventions while being strict about the graded values themselves, because a misparsed clarity grade is a mispriced stone. Export has to produce something the recipient's system will accept without a phone call.

This is the least glamorous part of such a system and one of the most used.

The general lesson

The instinct when meeting a domain like this is to force it into the shape of a product catalogue, because that is what inventory systems look like.

The stones are the counter-example that makes the general point visible: when the thing being sold is individual rather than fungible, quantity, SKU, substitution and a single price all stop being valid, and every one of them is baked into the default design. Recognising that before the schema is written is worth more than any amount of work afterwards.