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:
progression has multiple chords
chord can belong to many progressions
chord has one
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:
- is this over-normalising my data?
- If I do create this relationship table for
chords, does that change how I reference
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.
No, that should be fine. Look into the many-to-many Ecto docs, they have examples that look similar to your code.
That’s a relief, thank you very much for the sense check
Still, have in mind I said “similar”, not 100%.
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.
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?
You’re right, it definitely feels like over-normalisation to me too, but I want to separate the
ii) from the
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
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
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.
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…
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 I’ll certainly go with your suggestion for the