How do I design data structures with multiple join tables?

Questions / Help
, ,
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.

2

No, that should be fine. Look into the many-to-many Ecto docs, they have examples that look similar to your code.

1 Like
3

That’s a relief, thank you very much for the sense check :slight_smile:

4

Still, have in mind I said “similar”, not 100%. :wink:

Search for many_to_many on this forum and you can find plenty of inspiration. People have stumbled upon it a good number of times and their threads are IMO illuminating.

1 Like
5

Unless you’re doing some truly revolutionary music theory, making numerals a database table (versus an Enum) seems like over-normalization. What data is on that table?

3 Likes
6

You’re right, it definitely feels like over-normalisation to me too, but I want to separate the numeral (e.g. ii) from the extension e.g. m7. I was thinking a numeral table would have the numeral and the tonality (major or minor), which would determine what extensions could be used. I suppose the alternative is the chord table containing all possible combinations of numerals and extensions, e.g. I, Imaj7, I6, Imaj9, iim, iim7... etc. It’s certainly not going to be a huge amount of data but this really isn’t my strongest area so I’m certainly open to any suggestions!

For wider context, the way that this will be used is that the progressions will be generated according to some logic (that’s not really important right now) for a user-selected key and complexity. Each extension will have a complexity rating between 1-5 allowing for easier or more complex chords. But the main logic will be selecting the numeral of the chord (e.g. generate a ii, vi, IV, V, iv, I, ii, V progression. The extensions can then be generated based on the tonality of the numeral. Hope that makes sense :slight_smile:

7

Preface: all of the below assumes that you’re aiming to generate chords in a fixed key, using only pitches from that scale. Dynamically changing key or including non-scale tones will complicate things significantly.

Assuming you’re keeping to in-scale pitches in a single key, some of the notation is over-determined - for instance, in C major the ii chord (D/F/A) is in-scale but the II chord (D/F#/A) is not. Same thing for the sevenths; only the minor seventh ii7 is in-scale (D/F/A/C) while the major seventh iiM7 isn’t (D/F/A/C#).

However, the easiest way to teach SQL these rules is likely what you’ve described: write code that generates the full palette of possible chords and then pick from them. It could be something like a chords table:

  • degree - 1-7, the scale degree of the chord root
  • kind - major / minor / augmented / diminished
  • rating - 1-5, as you mentioned
  • additions - some way of representing “add a 7th”, “add a ninth” etc. Not sure what the query requirements are for this. :thinking:

Columns like kind can use an Ecto.Enum or similar instead of referring to a second table, since the set of possible kinds isn’t going to change.

You could extend this by explicitly modeling “common progressions” as a many-to-many table between chords, to create a graph structure like these progression flowcharts.

If you need to accommodate key changes, you could add a key column to the above since a iii in C major is distinct from a iii in G major.

Two further thoughts:

  • the contents of the chords table aren’t sufficient to 100% determine exact pitches & voices: “instrument A should play a C2, instrument B should play an E2, and instrument C should play a G2”. You’d also need to capture the desired inversion of the chord to pick which chord tone should be the lowest pitch.

  • this data organization is hard to search if the goal is smooth voice leading that avoids “bad” patterns like parallel fifths. For that, you’d want a schema that materializes all of the notes of the triad…

8

This has turned into a really interesting discussion, thanks! I guess a couple of extra points for context may help.

The first is that my main aim for this application is to get more comfortable with Phoenix and Elixir. To that end, I’m less worried about getting the perfect design than one that allows me to get up and running, and if I end up with something that could be released, all the better.

The second is around how I anticipated the chord progressions working. This is actually a reworking of an old dissertation I did, where I analysed sets of chord progressions in different genres to come up with the probability of one chord preceding another, then generated the progression from the last chord to the first. The chords do not have to be diatonic - for example, the out-of-key VII chord appears frequently, as does the III. My table of probabilities accounts for all possible two-chord progressions (e.g. I -> bii, I -> bII, I -> ii, I -> II etc.). I’m not intending at this stage to account for voice leading - I don’t care what the notes in the chords are. Obviously this will probably lead to some janky progressions but that’s fine for now at least. I also don’t plan to accommodate key changes for now, though this is somewhat mitigated by allowing non-diatonic chords.

That was just a bit of thinking out loud really, feel free to keep giving me your thoughts on this but equally you’ve been a great help already :slight_smile: I’ll certainly go with your suggestion for the chords table!

Thanks again