Same-table relationships

[M_J1]

Hi. For a music client I’m trying to set up a relational database that shows what’s essentially a genealogy of their genre, connecting the hundreds of artists they lay claim to based on influences and collaborations. Right now they’re all in the same table.

For example:

• Artist A influences Artist Z.
• An admin goes into Artist Z and associates Artist A as an influence.
• The record for Artist A automatically updates to reflect they have influenced Artist Z.

Sounds simple, but short of a convoluted flow of multiple duplicate tables I can’t figure out how to go about this. The other option is just an extreme amount of manual entry, which defeats the whole purpose…

Help?

[Nathalie_Collin]

Hi @M_J1,

Just link to that same table. I’m not sure exactly when that feature rolled out, but, without creating any other table, you can actually link to the same table. I believe it might have come out with the recent Gantt block as dependencies would require it.

[M_J1]

Thank you Nathalie, I will give it another go.

[JonathanBowen]

Hi @M_J1, @Nathalie_Collins - I think just linking to the same table doesn’t work, i.e. doesn’t create the reciprocal link:

I think the answer is a join table (joining the same table twice in this case). It does mean another table, but it will do the automated reciprocity…see what you think.

You’ve got a bands/artists table:

Then create a “pairs” table, linking the influencer and the influenced (both links to the Bands table):

Back in the Bands table you get the “Pairs” link twice and then you can add a lookup for each:

Now, when you add a new pair in the Pairs table it will populate the Bands table - influences and influenced by. (Hide the “pairs” columns to reduce the clutter)

JB

[Nathalie_Collin]

M_J1:

The record for Artist A automatically updates to reflect they have influenced Artist Z.

That is brilliant @JonathanBowen! :smiling_face_with_three_hearts: . I missed the two way visability.

I wasn’t really missing the ‘influenced by’ but rather putting it into assumption that the record itself was the ‘influenced by’ and the linked record was ‘influences’. No consideration was given that in Record Z, you would want to know that he is influenced by A - unless of course you do it manually which is pointless.