The Basics of Database Building

I’ve long been wrestling with what I want the ReSassy database to do. So far it’s congealed into the following goals:

• I want to track each piece of Sassy.
• I want to be able to summarize data from anywhere on a hierarchy of sub-assemblies.
• I want to be able to summarize data from a dynamic list of characteristics (“parts made of plastic”, “parts that were recycled”, “after-market parts” etc.)
• I want to track all the parts by weight and compare to the total actual weight and the 2094 pound curb weight. I want to be able to enter weight in whatever unit is convenient.
• I want to track financial exchanges for each part: either how much it was sold for, or how much it cost me to get rid of it.
• I want to note where a part will likely end up at the end of its useful lifetime, and to rank its utility during that lifetime and its value or destructiveness after that lifetime.

I’ll be using MySQL as that’s what Dreamhost offers and it seems to be a relatively useful and long-lasting database software. My MySQL skills are quite rusty but I know you create a set of tables that contain records of fields. The structure is defined by the tables and fields and the data itself is organized in records. I probably should diagram the whole thing, but I’ll forgo that step because I think the database will be pretty simple. Here’s the tables and fields I’ve come up with so far:

• Parts – one row for each part in Sassy
  • ID – number – a unique identifier > 0
  • Name – text – the short name of the part
  • isDefinitive – Boolean – true only if there is exactly one of this part in Sassy (i.e. “the engine” versus “a spark plug”).
  • PluralName – text – the plural name of the part (i.e. “wiper assemblies”) or blank if adding “S” works (i.e. “wheel”+”s” = “wheels”)
  • ParentPart – number – ID of the parent part if this part is a component of something else (i.e. “fuel injector” is part of “intake manifold”) or 0
  • Description – text – optional text of complete sentences of additional description
  • Weight – number – measured (or estimated) weight of part or 0 for unknown
  • WeightAsPercentage – number – estimated percentage of this part compared to the total parent part’s weight, 0 for unknown or between 0.0 and 1.0.
• Tags – one row for each unique tag name (can I just use the WordPress term/tag tables instead?!)
  • ID – number – a unique identifier > 0
  • Name – text – the real name
  • Slug – text – the HTML-compatible name
• TagLink – one row for each tag used in a part
  • ID – number – a unique identifier > 0
  • PartID – number – unique ID of part
  • TagID – number – unique ID of tag