3 relationships

[seraph_nyc]

Hi all,

I am making a database with three types of things. Performances, Performers and songs. There are 10 different ongoing performances. There are maybe 100 total songs and 100 performers.A performance is a collections of songs, performed between a range of dates by a set of people. A performer is someone who has done certain performances during a range of dates and know a set of songs. A song is a part of the performance and known by a performer. I am trying to link all these tables together to create histories with portals, but when I link more than two it asks to add a separate table. If I am in the performance table I want to see all the performers and the song histories, and the same in all the other views. It is a circle of relationships. How can this be done?

[Fenton]

You would need a join table, with an ID from Performances, from Performers and from Songs, one record per unique combo of these IDs; like the center of a wheel. You may need a couple of "constant" (based on 1) relationships, to facilitate adding Songs and Performers from Performances portals; unless they are already all entered.

I'm a little confused by the "performed between a range of dates" statement. I always thought of a "performance" as something that happened at one time. But what do I know -? If a range is needed, a start date and end date could either be in the Performance; or possibly a single date entered in each record of the join table, if you want to specify exactly when a song was performed.

[P.S. Sorry the above is a little vague; but it's late; mañana, as they say :-]

Edited April 15, 2007 by Guest

[Søren Dyhr]

It is a circle of relationships. How can this be done?

I'm agreeing with Fenton, this is a many 2 many structure, where each performance is a join table record. But as you have discovered isn't the RG an ERD so circular references are illegal, and the way it's done in "here" is sometimes to make a new TOG to facilitate this, in case it's going to be seen in browsemode...BUT if reporting is alright for the purpose, it's much more conveninet as a subsummary with start date as breaker.

Knowing the performers ID in the foreign key field and the range could a request be build and preview of the subsummary be shown. Either by storing the ID in a $variable or a GTRR from the artists record.

To be frank, isn't it 3 relations - but instead 3 tables and two relations, I'm afraid! Why bother with an extra TOG and even worse a pair of global fields??

--sd

[seraph_nyc]

Yes there are three tables and 3 relationships since it forms a circle, ideally. A performance is something which is ongoing like a baseball season, but each performer may leave a performance and then it is added to the history. I may have to re thing how this is all laid out and a separate history table for each one

Thanks

[Fenton]

I see it basically as 3 main tables, with a single join table between them; which has an ID from each of the others. There is always an Artist(s) and Song; many of them in a Performance.

Performances.fp7.zip

[seraph_nyc]

Ok Thanks. That shows me a list of performances with a list of artist and songs.

Now, I want to also got to the list of songs and see performances and artists. then to artists and see a list of songs they know and then performances they did. I would need two more join tables?

Edited April 16, 2007 by Guest

[Fenton]

No, you'd just look at the same join table from the other main tables. The IDs are in the join table, but the names are accessed THROUGH the join table to the relevant main table. This is a concept new to 7+, which (mostly) eliminates the need for "tunnelling" calculation fields in join tables.

But it's only 1 join table. Because an actual performance of a song, by a particular artist,* at a particular time and place, is only 1 thing, "entity" in database speak.

*If you list the current members of a group (band) performing the song, then it's just multiple records in the join table, 1 for each person. It could then be filtered through table occurrences to look like only 1 song. Or you could enter it differently, into other tables. I don't yet see the need; but I don't know all that you want to do.

Performance.fp7.zip

[seraph_nyc]

I will look at the database again. I think it is simpler than i think. I dont understand a few concepts since I started using filemaker a 7.0. So there are concepts which people throw around which are no longer an issue like data tunneling. Not sure what that is. Basically a performance is on going. But the songs and performers may change. I need to keep track of all these changes. So I created a history for each of the tables. So far it is working, I am just worried data may not be shared right.

[seraph_nyc]

Yeah I am understanding what you did. Not you have it set in a performance view, with a total list of performers and a list of songs. Now I want to go to each performer or each song and see a history of the other two. For instance go to each song and see who performed what and where. And go to each performed and see which songs they have done and where. If I understand I will need to at some point generate a extra Table.

[Fenton]

No, it's just 4 tables, the 3 main entities, and the join table, where they come together. There may be many instances of these tables on the Relationship Graph, several "table occurrences" (TO, like aliases). But that does mean more "tables."

In my latest file I gave each of the main entities its own Table Occurrence Group (TOG), which is a group of table occurrences only connected within the group. The main layout for the entity is attached to its "anchor" TO. This is the best structure for more complex files; worth understanding.

My latest file shows the participation in the Join table from each of the main entities. Its navigation is handled by opening a new window for each, for data entry and viewing. There's many variations of how data is entered.

It's kind of clumsy to do the new window routines in FileMaker 7; easier in FileMaker 8, using Variables for name, size and location; you could use just ScriptParameters, but Variables help.

[seraph_nyc]

I understand the concept, but I can wrap around how to make it work yet. The join table is where all the ids are keep. It is a unique record of where each song, place, person is kept. Then each other table access this master join thru portals. One question, I see the calculation =1 in a lot of relational databases, what is that for. Is that a show all function? Is there a good place to see this type of structure? I use fm 8.5 advanced and if I can understand this one thing, I am way ahead