Modelling Medical Device Size Variants as SKUs: A Ureteral Stent Example

#python #sql #healthcare #ecommerce

Medical device catalogues have a recurring design problem: one product, many size variants. A ureteral stent might come in six diameters, a dozen lengths and three materials, which quickly turns into dozens of SKUs. Here is a simple way to model that cleanly so search, filters and imports stay predictable.

The shape of the data

A stent variant is defined by a few attributes that buyers filter on: diameter in Fr, length in cm and material. Everything else, such as sterility and shelf life, usually belongs to the product, not the variant.

from dataclasses import dataclass
from itertools import product

@dataclass(frozen=True)
class StentVariant:
    diameter_fr: float
    length_cm: int
    material: str

    @property
    def sku(self) -> str:
        d = str(self.diameter_fr).replace('.', '')
        return f"DJ-{d}-{self.length_cm}-{self.material[:3].upper()}"

Generate variants instead of typing them

Typing every combination by hand invites mistakes. Generate the grid and then remove the combinations the manufacturer does not actually make.

diameters = [4.7, 6.0, 7.0]
lengths = [22, 24, 26, 28]
materials = ["polyurethane", "silicone"]

all_variants = [StentVariant(d, l, m) for d, l, m in product(diameters, lengths, materials)]

not_made = {(7.0, 22, "silicone")}
variants = [v for v in all_variants
            if (v.diameter_fr, v.length_cm, v.material) not in not_made]

The values above are placeholders. Use the manufacturer's specification sheet as the source of truth for the real grid.

Store variants in a relational table

CREATE TABLE stent_variant (
  sku           TEXT PRIMARY KEY,
  product_id    INTEGER NOT NULL,
  diameter_fr   NUMERIC(3,1) NOT NULL,
  length_cm     SMALLINT NOT NULL,
  material      TEXT NOT NULL,
  UNIQUE (product_id, diameter_fr, length_cm, material)
);

The unique constraint stops duplicate variants from slipping in during bulk imports.

Make filters friendly

Buyers think in ranges, such as slim adult or paediatric, so expose derived groups in your API instead of forcing every user to type exact numbers.

def size_group(v: StentVariant) -> str:
    if v.diameter_fr < 5:
        return "slim or paediatric"
    if v.diameter_fr <= 6:
        return "standard adult"
    return "large calibre"

Why it is worth the effort

Clean variant data means fewer wrong orders, faster quotes and simpler stock reports. To see how buyers actually compare these sizes, including diameter, length and material, read the full guide: dj stent sizes explained for buyers.

This is general software advice and not clinical guidance.

DEV Community