12 Replies Latest reply on Aug 22, 2011 4:49 PM by DanielPinder

    Help with (probably) simple relational db



      Help with (probably) simple relational db


      I need an idea for a database architecture that I'm sure you brilliant people can help guide me toward.

      I don't yet have the brain to figure it out by myself, as I'm still figuring out the relationship thing in FMP. Below is a simplified version of what I'm trying to build:


      1. The main table would track song titles. There would be one record per song.

      2. For each song, I need to track the length of time each instrument plays, so for example, for song #3, I need to enter the length of the piano (0:41), bass (1:03), guitar (0:45) & drums (1:32).

      3. I would prefer each instrument to be it's own record in a related table. This way, with each project I can add as many instruments as I need without having to create loads of specific fields.

      4. Every time I add a new instrument record, it adds itself to the list in my data entry layout. Portal perhaps?

      5. All instruments should be totalled somewhere so that for one project I can see the total mm:ss each instrument takes up.


      Here's a visual mock-up of what I'm after. THANKS for your help:



        • 1. Re: Help with (probably) simple relational db

           I'd handle it as a many to many relationship (songs can have many instruments, instruments can appear in many songs).


















          Instrument_Type (i.e. Woodwind,String,Brass,Percussion,etc.)


          And yes, you could use a portal to shows related rows from Song_Instruments on a "Song_Detail" type layout. The musical instrument ID could be a pop up to the musical instrument table in the portal itself. This is slight overkill from what you specifically asked but could do the job but I haven't tested it myself.


          • 2. Re: Help with (probably) simple relational db

            You could also include another related table for composer and/or artist since you may have more than one song by the same person or group. The basic idea here is to structure related tables so that you don't need to enter the same information more than one time, and it allows you to then list (report) all the songs by the same composer/artist and later calculate how often that composer uses the same instruments and which ones are played for longer amount of time. Lots of possibilities.

            • 3. Re: Help with (probably) simple relational db

              Dave S, thanks. I'm trying to piece it together, but I need to ask you what must be rudimentary stuff about the relationships. I connected them like so:



              How far off am I?


              Can I ask what the __kp and __kf designations are? I'm sure I'm going about it wrong and there's a clue in your naming scheme that I'm not picking up on. 



              • 4. Re: Help with (probably) simple relational db

                TECman, great suggestions. I think, however, I'll have to learn to crawl before I walk, but I can see all manner of cool things I could do with your ideas. Luckily, I only have to track the music of one composer in particular. The real point of this database is to track how much of each instrument plays in a film score, so I can help plan for time needed in recording sessions. For example, each "song" is really a cue, and I need to enter data for each instrument to be overdubbed. I appreciate the input—it helps me figure out what I really want this thing to do.

                • 5. Re: Help with (probably) simple relational db

                  Okay, I've done a little fiddling and I'm very close to achieving my basic goal. The one thing I'm trying to get the db to do is to display in the portal every record present in the table Musical_Instrument. In other words, if I create 6 records in Musical_Instrument, I want the portal in my main data entry layout to display the list of each of those records no matter what song (or cue) record I'm looking at. Furthermore, when I create a new song (cue) record, I want it to display the list of every musical instrument I have set up. What happens now is that if I want to display all of my 6 instruments, I have to fill in the music instrument ID number in the portal for each cue, from a blank portal.

                  Here are screenshots of how it looks at the moment.

                  • 7. Re: Help with (probably) simple relational db

                    Daniel, you are close, but not quite there. The kp_Cue_ID should be an automatically generated number which is an option for the field. That will make that step much easier. Inst ID should also be automatically created as you add portal fields. The instruments table could, for now, be edited on a separate layout and available to your portal as a pull down menu which then allows you to add a field or two from that table into the portal every time you select that instrument. 

                    The __kp_MusicalInstrument_ID should also be automatically created.

                    Personal note: unless you are planning to share this database with other Filemaker developers, you really don't need to use long field names or underlines. Example: kp_Cue_ID for your use could simply be Cue ID ... and so on.

                    • 8. Re: Help with (probably) simple relational db

                      kp stands for primary key. kf stands for foreign key. A field, typically a serial number field in FileMaker systems, that uniquely identifies each record in a table is your Primary Key. The field it links to in a child table, (such as your instruments table), is a foreign key field. (It's a key but the value is "foreign" to the table in which the foreign key's field is defined.)

                      This naming convention is very useful even if you do not share this file or descriptions of it with other users. The _kf and __kp are designed so that when you sort your fields names alphabetically, the primary key will be listed first with any foreign keys listed immediately afterward. This can be very handy when working with them on the relationships tab in Manage database as well as in other dialogs that list fields by name in your database. (If you choose to sort fields in Manage | Database | Fields, this sorts them in the same order on the Relationships tab and in a number of other dialogs in FileMaker.)

                      Just keep in mind that it's a naming convention to help you use the system more easily and to know the purpose of these fields when you are selecting them in script steps and calculations. The presence or absence of these characters in the field names won't affect how they function and there is no law that states you have to use this convention.

                      • 9. Re: Help with (probably) simple relational db

                        PhilModJunk is exactly correct. Naming conventions and consistency is important and does help keep everything in order. I sometimes simplify my naming of fields especially for small solutions, and instead focus more on the business and result aspects of my thinking. PhilModJunk's explanation is well stated and worth remembering. 

                        • 10. Re: Help with (probably) simple relational db

                          Great rudimentary info that I would do well to internalize—thanks.


                          Still working on this, have some more questions in a bit.

                          • 11. Re: Help with (probably) simple relational db

                            Okay, I've had time to return to this project and there is one major thing I'm trying to achieve that I haven't been able to and I'mhoping you can help. An update:

                            1. I can create cues in the "Cue" layout. Each cue gets assigned an auto-generated 3-digit ID number ("001, 002, 003").

                            2. I can create instruments in the "Instrument" layout. Each instrument is assigned a unique 4-digit ID ("1001, 1002, 1003").

                            3. The way I total up the times works fine.


                            What I need is a way to display in a portal (or set of related fields) each instrument record that exists, and to display the timings for the current cue. What's happening now is that every time I create a cue record, the portal goes blank. What I want is for every cue record to display, as though globally, each instrument record and then I can enter data pertaining to each record directly in the portal.

                            The only way I can get each cue record's portal to display the Instrument records is to enter their Inst ID number directly (I have to type "1001" for example), then each instrument fills up a line in the portal, but this is too tedious. 

                            The reason for making this relational in the first place is this: If I had 4 instruments I needed to keep track the length of, it would be easy to simply create a flat file db with 4 time fields and then create 4 summary fields to display each of the total times. The problem is that I have up to a hundred instruments to track and for each project, they will vary, so a relational db is the right thing. How can I constantly display all those related records, automatically?

                            Thanks again, here's what it looks like now:

                            • 12. Re: Help with (probably) simple relational db

                              And here's what a newly-create cue record looks like. Notice I had to enter "1001" in the top portal line to make it display the instrument: