How do I design data structures with multiple join tables?

Questions & Help Questions
, ,
1

Hi all, I’m learning Phoenix and building an application that creates musical chord progressions, but my relational data skills are a bit rusty (see my previous question).

I may not have phrased this question very well but I’m trying to achieve a data design something like the following:

  • A progression has multiple chords
  • A chord can belong to many progressions
  • A chord has one extension
  • An extension can belong to many chords

In my initial design I didn’t have a separate extension table - extension was a column on the chord table. I used a join table called progression_chords with the following schema:

create table(:progression_chords) do
  add :progression_id, references(:progressions)
  add :chord_id, references(:chords)
  add :index, :integer

This works fine and allows me to create records in the progression_chords table that reference the id of both a progression and a chord. But now I want to further normalise my data and move extensions out into a new table. I suspect that chord should now also be a join table, and I should have a numerals table, creating a chord schema like:

create table(:chords) do
  add :extension_id, references(:extensions)
  add :numeral_id, references(:numerals)

So, eventually, onto my two questions:

  1. is this over-normalising my data?
  2. If I do create this relationship table for chords, does that change how I reference chord_id in progression_chords (i.e. can I reference a join table in a join table)?

Apologies if this is a bit rambling, happy to clarify if needed.