So I hope my question here isn't too vague.
I'm currently thinking about how to model a bike with all its parts and their relationships to each other within a database.
I haven't chosen a specific database as of yet. So it could SQL, document or graph database, or whatever else I'm not aware of.
So let's say I want to represent a bicycle. A bike is composed of various parts (or components) which all relate to each other and all have various properties.
For instance a frame can have a 135mm rear dropout, which means it will relate to a rear hub that is 135mm wide. The hub itself has 36 holes which means it can be used with a 36 holes rim, and so on and so forth. However a frame could be disc specific (it has disc mount but no rim brake mount) which means it can only take a disc hub. However a disc hub can be mounted on a frame that does not have disc mount but uses rim brakes.
I have already explore the idea of using a realtional database (postgres) with a json type and single table inheritence. As in a frame and a hub are both component sharing common properties (model, make, material, year…) stored in a component table as well as specific value (size, number of spoke holes…) store in a json field within the component table. But the problem is to represent the relationship, I could either match unique properties or use foreign keys for each rows.
So my question here is, is there a better way to do that?
Best Answer
Are you documenting a tear-down of an specific existing bike or presenting options for the build of a new bike from available components?
For the former, a tree representing the bill of materials is the solution. There are several ways to represent a tree in a relational database. And, being a tree, a graph DBMS is a natural fit.
To build a bike from options, as in a shopping site for customizable products, a different approach is required. Here the basic units of design are the "precludes" and "requires" relationships. For example, choosing disk brake hubs "requires" a disk-compatible frame. Choosing a 36 hole hub "precludes" a 20 hole rim. Often it is an attribute of a part that determines applicability rather than the full part number. So we don't say "part XY123 precludes ZQ987" rather we say "frames that are RED cannot have saddles that are leather."
You must also list the categories of items that must all be chosen to form a complete product and those that are optional e.g.
Given the likely combinatorial explosion it is best to start from a root category (the frame?) and define the choices from there, rather like those choose-your-own-adventure books of yore.
In the abstract this can be modeled as node-types (frame, hub, chainset) with specific instances of each type (Trek steel frame, Norco carbon frame). The connections between the nodes form as denser, more linked graph than in a tear-down.