Alright David — I’ve ingested the entire spreadsheet, and here’s the good news: you already have all the raw ingredients for a world‑class organ‑pipe database. What you don’t have yet is structure. Your sheet mixes:
• physical pipe geometry
• musical identity
• rank/stop identity
• instrument identity
• wiring + controller mapping
• presence/condition
• voicing notes
• and even percussion + function controls
…all in one giant flat table.
So the job now is to separate these into clean, relational entities that match how your contraptions actually work.
Below is the schema I propose, derived directly from the fields in your spreadsheet.
---
🎼 PIPE DATABASE SCHEMA (derived from your spreadsheet)
This is the structure that will let you track every physical pipe, every rank, every instrument, every wiring assignment, and every voicing/tuning event.
---
1. Instruments
(“Busker”, “Estey 1318”, “Juliana”, “Topsy”, etc.)
Fields:
• instrument_id
• name
• description
• location
• notes
Why: Your sheet clearly groups pipes by instrument. This becomes the top‑level entity.
---
2. Ranks / Stops
(“Bourdon”, “8’ Saxophone”, “4’ Transverse Flute”, “Strings”, “Stopped Diapason”, etc.)
Fields:
• rank_id
• instrument_id (FK)
• rank_name
• pitch (8’, 4’, 16’, etc.)
• family (flue, reed, string, percussion, function)
• construction (open, stopped, chimney, reed boot, etc.)
• material (Spanish cedar, spotted metal, etc.)
• notes
Why: Your sheet repeats rank names constantly. These should not be duplicated per pipe.
---
3. Pipe (Physical Object)
This is the core table. One row = one physical pipe.
Fields derived from your sheet:
Identity
• pipe_id
• instrument_id (FK)
• rank_id (FK)
• section_name (“Small Front”, “Side Pipes”, “Bass”, “Melody”, etc.)
• present? (Yes/No/Missing)
• comments
Musical identity
• note_written (“A”, “C#”, etc.)
• octave_written
• midi_written
• note_actual
• midi_actual
Geometry (rectangular)
• cut_depth_in
• internal_width_in
• internal_depth_in
• front_width_in
• front_cut_length_in
• stopper_width_in
• stopper_depth_in
• front_back_thickness_in
• side_thickness_in
• length_in
• internal_depth_in (you have this twice; we normalize)
Geometry (round)
• round_internal_diameter_in
• round_aperture_width_in
• round_aperture_from_bottom_in
• round_diameter_in
Material
• material (wood species, metal type)
Tuning / voicing
• tuning
• generator (if applicable)
Wiring / control mapping
(You have a LOT of this in the sheet — this becomes its own FK table, see below.)
---
4. Wiring / Control Mapping
(“Cable”, “Connector”, “Destination”, “Dest pin”, “Controller”, “Decoder”, etc.)
Fields:
• wiring_id
• pipe_id (FK)
• cable
• connector
• destination
• dest_pin
• controller
• decoder
• generator
• notes
Why:
Your sheet mixes physical pipe data with wiring harness data.
These should be separate so you can rewire without rewriting pipe geometry.
---
5. Pipe Placement
For instruments with chests, pallets, magnets, etc.
Fields:
• placement_id
• pipe_id (FK)
• location_name (“U1”, “R3”, “L4”, etc.)
• channel / hole number
• notes
---
6. Pipe Log (History)
This is your “pipes log”.
Fields:
• log_id
• pipe_id (FK)
• date
• event_type (installed, removed, tuned, repaired, revoiced, measured, rewired)
• details
• person
---
7. Percussion / Function Elements
Your sheet includes:
• Glockenspiel
• Triangle
• Snare
• Celeste reset
• Pan flute
• Piccolo
• “Function” stops
• Control signals
These are not pipes, so they get their own table.
Fields:
• element_id
• instrument_id
• type (percussion, function, control)
• name
• midi_note
• wiring_id (FK)
• notes
---
🧠 How I derived this (citations)
Your spreadsheet shows repeated patterns like:
“Instrument / cut depth inches / section name / internal width inch / Voice / internal depth x2 / Present?”
“Cable / Connector / Destination / Dest pin / Controller / Decoder / Generator”
and also:
“Melody / Bourdon / A / 5 / A5 / 81 / 81 / Spanish Cedar / Normal / Stopped”
These clearly represent different conceptual layers that should not live in one table.
---
🚀 Next step (your choice)
I can now:
A. Generate the exact Drupal content types + field_machine_names
(so you can paste them directly into your contraptions site)
B. Produce a normalized SQL schema
(ready for MySQL/Postgres/SQLite)
C. Map every row of your spreadsheet into the new schema
(so you can import cleanly)
D. Identify duplicates, inconsistencies, and missing data
(e.g., missing pipes, mismatched MIDI numbers, repeated rank names)
Which direction do you want to go next — Drupal, SQL, or data cleanup/migration?
- Log in to post comments