3

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?

m0g
  • 31
  • 1

2 Answers2

1

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. 

bike = frame + wheel (x2) + seat + chainset + handle bar (all "must")
       + bell + carry rack (both optional).

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.

Michael Green
  • 25,255
  • 13
  • 54
  • 100
0

Assemblies are generally represented as trees. The root node is the whole bike. It has a child nodes - frame, fore wheel, aft wheel, handlebar, the seat and so forth. The wheel node has another childs - rim, tire, spokes etc. But that approach allows to set the relations between units. A contains B, C and D, D contains E and F, F contains X,Y and Z - down to the atomic parts. This is usually implemented by table of two columns - node_id : parent_id.

You need also an "assortment list" table that contains all the possible atomic parts in all their variety. All kinds of frames, rims, tires etc.

And finally you need the "bill of materials" for the certain setup: "node F is a rim A27b14/2", "node Z is a frame AA123" and so on. Some parts like nuts or bearings can be mentioned more than one time here.

To maintain the compatibility of the parts you need some additional information associated with the parts. I think that can be achieved by another table part_id : property : value. Each part can have one or more properties. Say part nut can have property size and value 3/8, while part hub can have properties width and num_of_holes. Then you can join the part with the assortment list looking for rows where properties of the same name have the same value.

But I've never gone so far with the assembly lists so I can't advise some proven recipe for your case.

Kondybas
  • 4,800
  • 19
  • 16