Help Configuring Tables

[Jason_Jenkyns]

Greetings,

I’m likely overlooking something simple but I need to find a solution to the following problem.

I have a table of local bands and a table of local musicians. They are linked so that I can add each musician as either a member of the band or a songwriter in the band.

So far this works fine. But I need to populate other tables with names of the local musicians. For example, I’d like to have a table called “local songwriters”, that is populated automatically with names from the local musicians table when a musician is listed as a writer in the band table.

Either that or I need to be able to categorize local musicians into either a. songwriters, and/or b. bandmembers so I can then take their names, not from the local musicians tables, but from the table of songwriters or the table of bandmembers.

Furthermore, I need a list or table called “Session musicians” also to be populated with names from the Local musicians table.

I’m not sure how to structure this.

Thanks!

[Tech_Tylercivic]

Reset thinking. Have a “People” table. If they appear in a songwriter table, they are songwriters. If they appear in the musician table , also Musicians. If their zip code is in a certain range, they are local. And so on.

[Jason_Jenkyns]

The “musicians” table is the “people table”. There are no “people” who are not also musicians. The musicians should populate tables that are effectively sub-categories, as you suggest; those who are writers, those who are band members (that do not write), those that can be called upon for cowriting sessions, and those who can be called upon for session work.

How would I populate the songwriters table, session musician tables as you describe?

[Elias_Gomez_Sai]

I recommend an extra table to join Bands and Musicians:

• Bands: Name, year of start, city, etc
• Musicians: Name, age, height, etc
• Musicians_in_Bands: The ‘real’ Bands groups, where you have a record for every Musician that belongs to a Band. This way you can do more advanced Filters, Formulas, and so on. Also you can add data like ‘year entered in the band’ (or exited); function, your Songwriter/Member; Instruments played in that band, etc…
• Sessions: with a Link field to the Musicians table. If you want to store more data, e.g. Food that a Musician have to bring to a specific Session ( :grinning_face_with_sweat: ), you should have an extra table as in the previous one.

Anyway, what do you want to do is done with Views and Filters, you don’t need to populate other tables.

An official article: https://support.airtable.com/hc/en-us/articles/218734758-A-beginner-s-guide-to-many-to-many-relation...

[Jason_Jenkyns]

Thanks to you both.

I’ve read through all the support material and I understand how it is being used in the examples. I see the logic in having a bridge table as you suggest.

My problem lies with how I’m thinking about the primary fields. As I can see there is no way to have 1 class of thing belong to one or many other subclasses while retaining the same primary field name.

This is what I’m looking for here: http://www.tomjewett.com/dbdesign/dbdesign.php?page=subclass.php

See bottom up design with accompanying diagrams. If I set this up is there a way I can convert ID #'s back into names using a lookup table or something?

[Elias_Gomez_Sai]

I don’t know why a single table for Musicians does not work for you. What kind of property do you want to store only on a Songwriters or ‘Standard’ musicians? Why can’t you store that data in the Musicians table (leaving empty when not necessary)?

I understand that it’s more efficient to have a subentity (subtable), but I think this service is not a “real database” tool. Let’s make an example: you could create a Songwriters table, where you Link to a Musician, and you could have a Field called “Songs composed” (which would be a Number), and then you could Lookup to this from Musicians table, or even create Lookup fields on this table to get data from Musicians tables. I think it’s better to have all together in the same.

I don’t know more :hugs: :pensive: