Jump to content
Sign in to follow this  
seraph_nyc

3 relationships

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.