Work · 03 of 04
From price lists to a catalogue database
How a drive full of supplier price lists became a structured relational catalogue, and how I audited every product on the website against it.
The starting point
Product information lived in a drive of folders, one per supplier, each with a yearly price list covering every product and every configuration option. The website showed only part of it, and nobody had checked for a while whether it was still current.
The data model
I designed a normalised relational model. Each supplier has its own products. Products sit in a hierarchy of sections and subsections, agreed in meetings with the business team and stored in one self-referencing table. Each product carries bilingual text, a main image, and flags for showroom and display order. Shared assets such as finish samples, dimension drawings and technical files are stored once and linked to products through junction tables, so one change updates every product that uses them (a single source of truth). Prices are deliberately kept out of the catalogue: it is for discovery, and price comes from the supplier’s own configurator.
One idea at the centre
an attribute–value model for options
Products have several kinds of configuration options, and these differ from supplier to supplier. Instead of adding a column for each option, I defined each filter once, with a controlled list of values, and tagged every product with the values it has through a many-to-many table. A new filter is new rows, not a schema change. It also lets visitors narrow the catalogue the way they think about furniture.
The data audit
I audited the catalogue supplier by supplier. I removed records for products no longer made and for ones that had not sold for years, and I added trending products that were missing from the site (data cleansing and a coverage check). Then I assigned each product its filter values, based on the options it really offers.
What it makes possible
Visitors can filter the catalogue. The team manages products, finishes and files from an admin panel. In the internal CRM, a quote line can reference a catalogue product or be entered by hand when the product isn’t on the website.
What I’d tidy next
- Sections reference their parent through a field that is stored as text, so the database can’t enforce referential integrity there.
- Older tables use sequential numeric keys and newer ones use UUIDs, and I’d align them.
Both work today.