aNerdInTheHand

How do I design data structures with multiple join tables?

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:

is this over-normalising my data?
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.

7 comments

/phoenix #ecto #data-structures

0 806 7

2023-05-11 09:53:22 UTC

Marked As Solved

dimitarvp

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

Post #1

Also Liked

al2o3cr

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?

Post #4

dimitarvp

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

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.

Post #3

Last Post!

aNerdInTheHand

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 chords table!