Revisiting portals, relationships and more

Curated Collections Cases Study
, , ,
1

First, I appreciate any help I can get as this project has been evolving over two years and, as these kinds of projects to, has become more involved and complicated over that time. I'm not a programmer, but a sort of user nerd, so my inquisitive mind wants to learn, but sometimes simple concepts elude me. Feedback, both on the specific goal at hand as well as the project overall is always appreciated.

Performers is one piece of a much larger project, but I'm trying to enhance the project and working on a piece at a time.

BACKGROUND

I'm sharing the entire project layout to provide some insight into how the parts fit together. The application is a booking management tool for performers, consisting is several tables as shown:

image

Again my focus right now is the Performers table and how to use it in relation to the Gigs (and Gigs~Songs_Join) tables.

There is a Gigs layout which is the hub for performances by one or more Performers. On that layout is summary information about a gig and a portal which shows which songs were performed and in what order (Set & Slot fields). The portal also includes a Performer Name. The assumption at this point is that up to 4 performers will do a given gig. This might change in the future, and could impact the below goals.

Gigs~Songs_JOIN is a connecting table for Gigs, Performers , Songbook, Venues, Musicians, etc. Performers is a table of singers and includes fields for a serialized SingerID, name, area of speciality (Tenor, Baritone, etc.), etc., and additional fields for combinations of the same performers including:

Multi: checkbox-indicates this record is more than one performer
PerformerName: calculated based on Multi: if NOT Multi, show performers Name, if Multi, show MultiName (below)
Type: value list-is this a duet, trio, quarter, all, etc.
MultiName: the description of this record (i.e., Duet-Clayton/Vicky)

Other summary fields include:

Total Gigs: count of gigs in which this person has performed
Total Songs: count of songs this person has performed
GigID: match field to Gigs~Songs_Join

image

GOAL

I have two goals, one related to the other. First, if the Performer record is checked Multi, I want to "attach" or "associate" individual singer(s) to the record (i.e., show what people are "included" in the MultiName). For example, MultiName=Duet-Clayton/Vicky, I want to show that Clayton and Vicky (each in separate records) are part of Duet-Clayton/Vicky.

Second, ultimately on the Gigs layout, I want to show which Performer sang which song (currently works in the portal), but more importantly, show variable summary information (goal layout below) which changes for each Gig as to each Performer, how many songs they sang and the total amount of time they sang. The catch is, in the summary I don't want to show the Multi records, but instead increment individual singers. So, in the summary info with related portal records in the example below, rather than showing Clayton sang one song, Vicky sang one song and Duet:Clayton/Vicky sang one song, I want to show Clayton sang 2 songs and Vicky sang 2 songs. In summary info, Duet:Clayton/Vicky wouldn't appear. In othe words, the summary would show how much time each person was singing.

image

In the summary block of the Gig's layout, each of the above sets is a one-row portal filtered by set. I want to have the SINGER1,2,3,4 replaced with the actual Performer's name, and each band/portal show summary info for the appropriate singer.

This is my relationship graph:

image
image1000Ă—445 78.1 KB

2

Performers are vocalists and Musicians play instruments? If so, you may want to consider simplifying your graph by combining them into a People table.

3

No, the musicians table is a collection of original artists, composers, cover artists, etc., for songs in the Songbook table. While some performers DO play instruments, in the context of the project, they would be performing on their instrument (as opposed to singing). This only happened once, so an actual musician was added to the Performers table. Does that make sense?

4

The 'principal performer' and 'accompanist' distinction could be done in a single relationship:

Gig <---> Gig_Song_JOIN <---> Performer <---> Musician

In the 'Performer' table, a field 'Role' (using a value list with values 'Lead' and 'Accompanist') could be used for distinguishing between the two.
The 'Performer' table could also serve as a link (JOIN table) between Gig_Song and Musician, thus allowing for recording of the specifics of the performance of each artist involved in a performance, like Instrument (vocals, guitar etc.) length and so on.
This graph design uses the 'People' table concept, @steverichter suggested.

1 Like
5

Does that make sense?

Yes, although you still may want to consider consolidating all people into one table to simplify your structure.

1 Like
6

Agreed, a PERSON Table with a discriminator field for the "type" of PERSON.

1 Like
7

I could certainly combine Musicians and Performers. I just thought it was easier to keep them discreet, both in terms of clearly understand what each contained, and in terms of having one less filter to manage when pulling related records.

But I'm also thinking I didn't clearly articulate my goal. Everyone in the Performers table, regardless of specialty (singer/pianist, etc.) performs at a Gig.

Musicians are strictly people who wrote the song, originally recorded it, composed or wrote lyrics, etc.

That said, my goal is still to show the summary totals in my Gigs layout and to distinguish a Performer (Singer) from a Performer (Multi). The idea of a people table is intriguing...

@Torsten, you mentioned a graph ,but I didn't see an attachment. Should there be one?

8

In this case It makes total sense to keep them discreet since they are not all people for the purpose of the database. The performers are all performing artists regardless of type of performance (especially since many can perform various type of art).

The other table should be « œuvre » a table containing all the details about each composition work/creation where the creator(s) are listed. Or you could keep œuvre creators and œuvre details as two separate tables if you have to calculate royalties.

So you can have oeuvre, creators/composers whichever you prefer, and performers and gigs/performance.

Gigs being a join between Ĺ“uvre and performers. Composers remains related through its foreign id in the table Ĺ“uvre

1 Like
9

@cpking

I am very interested in your post. As a working freelance musician for just shy of 30 years, I have looked at this business from the slightly different perspective of being a performer contacted by any number of band-leaders -- but, the tracking of gigs, venues, repertoire, contacts, etc. is all very familiar.

I re-read your post several times, and I repeatedly stumble at understanding a few key concepts about your current set-up. Some things that you have said seem potentially contradictory to me, but it could be that I just am unaware of enough of the context to make sense of it.

Regarding the concept of consolidating your Musicians and Performers table:

In general, I agree very much with the approach of consolidating Musical Contacts into a single table, and using some sort of field to distinguish between contact types.

I think, however, that the name Musicians has misled many of us to think that the Musicians table holds musical contacts -- but I have my doubts that this is the case, doubts which are reinforced by your most recent post. If I were to name the "Musicians" table, I'd probably want to call it something more like "Song Attributions" to make it clear that this table tracks composers, rights, and credits, which, in some cases might not even be people, but could potentially be some sort of business entity. If this is the case, one could still get away with using one table to store this data along with the performers being contracted, but I would say that it's much less of a natural fit. And, in such a case, I would probably avoid it, but (personally speaking) I would probably want to give the table a name which is less prone to ambiguity or confusion.

Other questions for you:

Gigs~Songs_JOIN is a connecting table for Gigs, Performers , Songbook, Venues, Musicians, etc.

This is not really true, is it? It appears as though the join table mentioned above does not associate Venues to the Gig. The Venue association is happening through a Venue foreign key in the Gigs table, correct? When I read the above statement, it was cause for confusion/concern, but I think perhaps it was a generalization which is not absolutely precise with respect to Venues. Leaving the above statement aside, and then just looking at the left portion of your relationship graph (thank you for posting that, it helps a lot), I find the arrangement reasonable.

Having said the above:

I know that you've stated a goal about the sort of song counts that you'd like displayed at the gig level. That makes sense to me. But, before even looking at that, I'd really like to focus a bit more on the Performers table. I have a concern about your present structure whereby an individual Performer record could have a status which is "individual" or "multi". My concern is that you might be better suited to have a separate table which tracks something like "PerformingUnits" or "Ensembles", each of which is a collection of 1 or more MusicalPeformers. And so I'd like to open up the discussion on this a bit, to see why/how you have adopted the current structure that allows for the "multi" or "individual" status. I think it would be good to identify both the advantages and limitations of this design choice, and to brainstorm about other structures. And -- I suggest doing so before tackling how to reach your stated goal for song counts on the Gig layout.

Hope this may help. I'll keep an eye on this thread, to see where it goes.

Sincerely,

-steve

3 Likes
10

Ĺ“uvre / Ĺ“uvres (French) = work / works (English)

Nothing to do with food (hors d'Ĺ“uvre).

3 Likes
11

I agree, in fact if we had to put together 2 entities in one table, i would put together venues and artists lol. It would be a contact table! But since there would be very few fields in common besides adresses and such, my preference is once again for small tables and keys (g minor... ok that was dorky) so performers, coordinates (addresses, phone etc), venues, labels...

12

Hello @cpking

Just a shorter note to say that I've had chance to look longer at the remainder of the relationship graph (beyond the left side), and it makes a lot of sense to me, and I think there is lot of good stuff there.

Really, the only part of the graph that I really would like to see vetted before addressing your goal is the Performers table (as I mentioned above), and the table which tracks what I'd call Set List Data, i.e. the join table between Gigs and Performers.

I mention the above both to highlight it, but also to let you know that so much of the rest of what I see makes great sense to me.

One extra question: Is there any provision to track that different vocalists may perform the same repertoire item, but in different keys?

All the best, and hope this helps.

-steve

1 Like
13

@Steve_ssh, thanks much for taking the time to reply. I'll attempt to address each of comments in order...

I think you are correct. Musicians might be better named Attributions. I tend to like one-word names for tables - likely a holdover from Access and the original Lotus 1-2-3 database (name eludes me). I type more than I point and click, so keystrokes are different for me - lol.

All this aside, I rather dislike the notion of combining Performers and Attributions; they're simply not the same thing, although I understand the confusion I may have created.

Regarding Gigs~Songs_JOIN, you are correct. Actually, the combination of Gigs and GigsSongs_JOIN tie it all together. I've been playing with this project for over a year and it has developed over that time as well, so sometimes my brain and fingers aren't in sync with each other.

With regard to the Performers table structure, thank you for your comments! By way of background, originally there were two singers in the system and no provision for adding more. So much of the data could be hard coded. There was Clayton, Vicky and Duet. Because there were only two people, Duet was obviously a combination of Clayton & Vicky. So summary fields could be used to count and total various things (i.e., duration/set length, # of songs, etc.). In that iteration, there wasn't even a Performers table.

Not we have multiple singers, which also provides multiple combinations of singers. Thus the Performers table. I had originally thought to do a self-join on Performers for the purpose of showing, by matching SingerID and Main in one table occurrence with another, who was in the "group,"

I'm intrigued by your idea of Ensembles (PerformingUnits sounds too much like the Borg to me-lol). Would you have an ensemble of one person in the case of a soloist? And assuming you would, use this Ensembles table to pass data to the Gigs layout, rather than Performers?

Whichever way ultimately serves the goal of summary reporting the best is the way I'd like to go,. And I've love to hear your thoughts on this.

1 Like
14

Hello @cpking

Attributions sounds like a great name for what is presently named Musicians.

Agree that PerformingUnits is a name that leaves much to be desired. I don't feel strongly about the name for this proposed table -- mostly just wanted to toss the idea out there. If I were to use it, I would indeed have there be such a thing as an Ensemble with just one member, despite any contradiction of nomenclature.

Whether or not having an Ensembles table would suit your solution best, I can not say -- but it is an idea worthy of consideration. I think that some of the trade-offs are apparent: It allows for flexible and varied combinations of performers. On the flip side, it adds a level of abstraction which I would want to make sure is justified.

1 Like
15

Thanks! It's been developed, with a great deal of thought, and with a great deal of online help from kind people like yourself!

What would you like to know? The Join table has GigID (linked to GigID in Gigs), Set and Slot, SingerID to connect to the Performers table, SongID to connect to the Songbook (for song name, durattion, genre and tempo), and a unique JoinID field.

On different keys, yes, that will be a future enhancement, I'll likely concatenate the starting key with the songname for the various different iterations.

1 Like
16

Don't know if this would be useful, but I found an online ERD tool that you could use to collaborate with the folks here in refining your ERD:

https://online.visual-paradigm.com/pricing/

(there is a limited capability, free, option)

There is also a SQL Editor that has an ERD capability that will actually reverse engineer your FMP database into an ERD. You can make changes to the (visual) ERD and it will "forward engineer" that back to FileMaker. Repeat as necessary.

https://www.malcolmhardie.com/sqleditor/

I've used it and it works!

($79)

HTH

2 Likes
17

I particularly like the capacity afforded by your solution to be able to see related material, or, for instance, to be able to put together a set list of tunes that feature a specific composer, or "original artist".

Posting the image of your relationship graph has helped a lot. I don't really have questions about how it presently works -- much of that is clear from the graph. The questions in my mind have to do with whether we could come up with a structure that serves the solution even better. As such, I am interested in things such as what your work flow is like when you create set lists, and what data you need to be able to see and select easily when you do so. And, whether you already have the Performers selected before creating the set lists, or does the material in the set list sometimes dictate the viable Performer(s). And then, once the set lists have been established, what information needs to be displayed and/or printed for reference by anyone who is on the gig.

These are somewhat vague questions, I realize. I am admittedly just trying to get a more detailed sense of your workflow to see if any revision to your current structure jumps out at me as preferable. And -- a large part of what motivates this is looking ahead a little bit to the types of aggregates that you stated that you would like to display.

18

Interesting questions! And frankly, as you might imagine, when we first started out, it was a matter of cataloging what we were doing. I think I mentioned it was initially just me and a singing partner, so it was a matter of managing set lists more than anything else. We booked a few gigs at a single location, and we chose songs to sing from our on repertoires. That first group of 20 or so songs got entered. As we continued to perform at that location, we added songs and, after booking a new location, suddenly needed to manage venues. Then our accompaniest couldn't do one gig, so we need the ability to switch out names. Typical evolution.

In early 2020 (before the pandemic) we did a concert with four singers which had me hard-coding new names which rendered the set summary info useless and which led to where I am now.

Typically the singers are the known quantity (beyond the location, of course). Most often there's a theme for the night (e.g. a night of Broadway, or a night of blues) and singers choose songs to sing. In most cases, a singer "owns" a given song. However, there have been a couple of instances where one singer sang another person's song because the normal singer couldn't be there. This led to the idea of NOT tying the song to the singer.

In this way, the Songbook grows based on singers introducing new material or discovering new material we'd like to perform sometime in the future. I hadn't thought about choosing music b a given composer or artist, although I love the idea.

Here is the Gigs layout. The song list is a portal which pulls info from the Songbook and Performers tables. Sets & Count is what led to my original post - summary information on the gig. The other portal (top right) is a self join just to provide a quick list of gigs for reference.

image
image1103Ă—894 86.4 KB

1 Like
19

There is a one-to-many relationship between gigs and sets, correct?

And a many-to-many relationship between sets and songs?

Finally, songs have a “type” attribute, correct? For example, solo, duet, trio, quartet. So there would be a one-to-many relationship between songs and performers?

20

Unless my first covid vaccine is making me loopy, I don't believe there are any relationships set up between gigs/sets or sets/songs. That is to say neither is in the relationship graph as being a match field.

The Gigs~Songs_JOIN table has records that include the set, slot (in a set) and SongID, but the SongID is used to pull the song name for a Gig Set List (Gigs layout).

Am I missing something, or confusing something?