About My Mechanical Database #62   ..
Originally I had a carefully written document called How Bikes Fit Together - 2018. It is not a mechanical database, it is only a document with chapters. The limitation was that a document can only reflect a single hierarchy, but cannot reflect variations. When trying to fit all the information into a standard document, the author has to make numerous arbitrary decisions as to where to put or repeat certain information. What I wanted was a stricter database structure, such that every piece of information would properly belong in exactly one place. Eg: If something is a part, it needs a Part record. And the part table can have a hierarchy, such that different variations of a part can each have a record. The main objective is to have a data structure that had sufficient definition that information would be filed in the same place, even by multiple people. Once everybody knows the rules, the database could be maintained by multiple people.

It took quite an effort to work out the correct data structure. The first snag I ran into is that there are two different hierarchies:

  1. Systems Hierarchy Eg: Steering System and variations
  2. Part Hierarchy Eg: Stem part can vary: Clamp Stem or a Quill Stem

When I say "Part" I really mean a generic part type" not a specific manufacturer. eg: A typical generic "part" is "Quill Stem". In my database, one record titled "Quill Stem" covers all manufacturers and models of Quill Stem. To know if any given Quill Stem is going to fit, you need to read about it's interfaces. (But that is getting ahead of things, first of all, let's discuss the 5 different "Entities" our database needs. The three main entities are "System, Part and Interface. And then there are two tables that link parts to systems, and interfaces to parts.

  1. System Table:
     A "system" is a method of accomplishing a "function" on the bicycle. All bicycles have a steering system. There can be "variations" of steering system, and each of these will have a sub-record linked to the parent "Steering System" Eg: Steering System has two variations: "Threaded Fork System" or the "Threadless Fork System". Subsystems have a field called ParentId. It implements the relationship "IS A". For example, a "Threaded Fork System" IS A Steering system.

  2. Part Table:
     The Part table also has a hierarchy. A Threaded Fork IS A type of Fork.

  3. Part To System table:
     The third table links parts to systems. (S_P link) It tells you which parts belong to each system. Which parts make up a system. eg: Every "threaded fork system" has a "Stem". The relationship is "HAS A". And the parts belong to the most specific (lowest level) Eg: Not every Steering System has a Quill Stem. But if you go down a level to be more specific, every "Threaded Fork Steering System" has a Quill Stem.
     
  4. Interface Table
     An interface is the specification for how two part types fit together. eg: the BSA Thread Interface is the way the Square Taper Bottom Bracket fits into the Frame. Note that every interface involves at least two parts. (1. The Bottom Bracket and 2. The Frame. For this reason, we need our 5th table which links parts to interfaces.

  5. Interface Link
     This table links interfaces to parts. It is a many to many relationship: An interface is always relevant to two parts. And a part can have multiple important interfaces.

     Now you can begin to see the power of having a database - we just need to specify the BSA Thread Interface in one place, but can be linked to both the Square Taper Bottom Bracket, and also the Frame. If we were just trying to document how bikes went together using a single hierarchy, we would have to repeat the same interface info in two places.

     


%>