7 Replies Latest reply on Feb 3, 2014 11:42 PM by erolst

    Music Cue Database Setup 3 Part Question

    sgtpeppas

      Hello,

       

      New to Filemaker Pro 13. I am setting up a databse of music cues in a catalog. Within the catalog there are numerous genres. The first two entities I've identified are CUES and GENRES. One cue can have many genres and one genre can have many cues. When a cue intersects one or more genres you have a CATALOG ENTRY. Here is how I've set up my Relationships:

       

      Screen Shot 2014-01-25 at 5.15.09 PM.png

       

      All Primary Keys are Serial, Auto-Enter, No Modify, Number

      Foreign Keys in the Join Table are just Text

       

       

      QUESTION 1) Does this look correct?

       

      QUESTION 2) If not, is there a better way to handle this?

       

       

      The other problem I am running into is figuring out how to simultaneously create new records in both the Parent Table CUES and the Child Table CATALOG ENTRY. When I create a new record in CUES the ID is auto-entered. I can fill in all of the fields just fine. Problem is I would then have to go over to CATALOG ENTRY and manually fill in the Foreign Key field (_CueIDfk) to bring the data through from the Parent Table. This certainly can't be the best solution to have CATALOG ENTRY mirror records from the CUES Table with thousands of music cues. Once I have a cue record in CATALOG ENTRY the intention is to then assign anywhere from 1 to 4 genres from the GENRES Table to each individual cue.

       

      QUESTION 3) Is there a filemaker solution to my filemaker problem?

       

      Thanks ahead of time for your assistance! Please keep in mind that the growth of my prefrontal cortex was stunted due to the high stress environment created by my dysfunctional family unit! In other words I am quite the novice!

       

      Regards,

      Paul

        • 1. Re: Music Cue Database Setup 3 Part Question
          TimGriffith

          My first observation is in your description in which you state your primary keys are auto enter NUMBERS, and yet your foreign keys in the join table are TEXT. These foreign keys, in my opinion, should be NUMBERS (as they are related to NUMBERS)... I would think the relationship would be broken otherwise.

          • 2. Re: Music Cue Database Setup 3 Part Question
            erolst

            sgtpeppas wrote:

             

            QUESTION 1) Does this look correct?

            Yes, except for the type mismatch between the primary and the foreign key. Will probably still work, but if you can, use the same type for matching fields.

            The other problem I am running into is figuring out how to simultaneously create new records in both the Parent Table CUES and the Child Table CATALOG ENTRY. When I create a new record in CUES the ID is auto-entered. I can fill in all of the fields just fine. Problem is I would then have to go over to CATALOG ENTRY and manually fill in the Foreign Key field (_CueIDfk) to bring the data through from the Parent Table. This certainly can't be the best solution to have CATALOG ENTRY mirror records from the CUES Table with thousands of music cues. Once I have a cue record in CATALOG ENTRY the intention is to then assign anywhere from 1 to 4 genres from the GENRES Table to each individual cue.

            CatalogEntry (CE) isn't a child table (in the strict sense), it's a join table (at least two parents). Each record states "This is a combination of one Cue and one Genre", plus possibly more details (fields), which are attributes not of any of the record's parents, but its own. Therefore creating a new entry in Cues and then a new related record in CatalogEntry doesn't make sense unless you also specify the second parent, namely a Genre.

             

            Also, the CE table doesn't need to “mirror” any of its parents; you can simply display (on the layout) or access (in scripts and calculations) the respective parent's data via the established relationships – either on a CE layout, or the respective “other” parent – without the need to duplicate them.

             

            If you set the relationship between Cues and CE to "Allow creation of related records” for CE, and display a portal into the CE table (occurrence) on the Cues layout, you can create child/join records simply by entering data into the fields within the portal row. The Cues foreign key in CE will automatically be set to the primary key of Cues, since these fields are specified as the match.

             

            Another approach is to use a list of genres, select the desired ones and add them in one fell swoop; or/and designate certain genres as standard and have them created on the press of a button, or automatically on creation of a new cue.

            QUESTION 3) Is there a filemaker solution to my filemaker problem?

            See if this sample file gives you some cues clues … (and never mind the funny colors)

            • 3. Re: Music Cue Database Setup 3 Part Question
              sgtpeppas

              Thanks TimGriffithStL! I didn't catch that. I also may not have figured out that the inconsistent field types would break the relationship. Appreciate your insights!

              • 4. Re: Music Cue Database Setup 3 Part Question
                sgtpeppas

                erolst,

                 

                Thank you indeed for the explanation and example file! Personally I am thinking that databases are better when they have a soul, and color aids in that endeavor!

                 

                I've made the minor adjustments to the Primary Keys and the Foreign Keys and made them all NUMBER.

                 

                I've also taken some time to examine the example database. I can't say I understand everything but I think I understand the basic concepts. The scripting is a bit wonky for me at this point. Here's a shot of where I've gotten the relationship graph to:

                 

                 

                Screen Shot 2014-01-29 at 11.57.11 AM (2).png
                At this point when I create a new record for an individual music cue I am doing it from a CATALOG ENTRY LAYOUT based on the CATALOG ENTRY TO. I have a portal on this layout bringing through fields from the CUES TO. And I am allowing records to be created and deleted in the CUES TO from the CATALOG ENTRY TO. As soon as I enter data into one of these portal fields it creates the new record. All of the Primary Keys are auto-enter and can not be modified. If I start all the Primary Key IDs at 1 then they increment together which is nice because then the CueIDpk and CatalogEntrypk have identical values. Now each individual music cue and it's essential attributes are stored in the CUES Table and those are joined with attributes from the CATALOG ENTRY Table to create an official CATALOG ENTRY.
                **NEW QUESTION**
                erolst, I looked underneath the hood at your example and I noticed a lot of scripting that allowed a user to select a Genre from a list of Genres. It enable them to select 1 or 3 or all and then it would poplulate these selections into a neighboring portal. In addition you had a delete button that allowed the portal row or Genre selection to be deleted without deleting the record from the Genre table. As I mentioned, the scripting is a little beyond me at this point but that is certainly nifty. Now that the basic structure appears to be there for the Parent-JT-Parent relationship of CUES-CATALOG ENTRY-GENRES the next step is to figure out the simplest way of doing three things:
                1) Create a Conditional Value List drop down that allows a user to select a Genre for each cue and then have the Subgenre drop down be dependant on the choice made in the Genre drop down.
                Ex: I select ACTION for the Genre and then only the Subgenres of ACTION appear in the drop down for Subgenre. Like ACTION BUILD, ACTION TRAILER,...
                You can see from my relationship graph that I've already created a copy of the GENRES TO called GENRE1_CVLTO. I've set this up, I believe successfully, to create the drop down scenario I've described. I've attached my database so you can check out the details. This allows me to assign 1 Genre and 1 dependant Subgenre to a cue in the CATALOG ENTRY. That's great but...
                2) What if each cue can have anywhere from 1-4 Genre-Subgenres at a time? I could set up 3 more of these TOs and continue to relate the Genre field in the CVLTOs to Genre1, Genre2, Genre3, and Genre4 in the CATALOG ENTRY TO . But then when I try to create reports on say how many cues are in the ACTION Genre or how many cues are in the Subgenre ACTION BUILD I think I will be unsuccessful because the TOs are not related in the necessary way and my data would be incomplete if I just base it on one TO. ??
                3) I don't want to alter the data in the GENRES Table. I just want to display it or apply it to a cue from 1 - 4 times in the CATALOG ENTRY Layout. These are Genres and Subgenres, they don't change unless at some point you reorganize the catalog. At that point you would just go in and change the records directly in the GENRES Table. And you certainly wouldn't want a user to be able to delete a Genre record from a portal on the CATALOG ENTRY LAYOUT. Music cues are different, you want to be able to add and delete those. How can you construct the database taking into account all of these factors?
                Hearty warm grateful thank you for your participation!
                • 5. Re: Music Cue Database Setup 3 Part Question
                  erolst

                  Mr Kite Lucy sgtpeppas –

                   

                  if what you say is true, then I guess my sample database has the soul of a rambunctious four-year old who loves her crayons very dearly …

                   

                  Anway, you sure have a lot of questions …

                   

                  I've looked into our database. First thing: don't create field1, field2, field3 …. and even more alarming is fieldA1, fieldB1, fieldA2, fieldB2 …

                  This is (almost) always a sign that you should use a related table with fields A and B; this structure makes doing all the things you could potentially want to do with those fields much easier (though at first glance it may seem the opposite).

                   

                  So, in your case you want to be able to add an (unlimited) number of genres and sub(-genre)s to a Catalog entry, and you want to associate subs with genres.

                   

                   

                  First step: many Genres (not as fieldA1, fieldA2 …). The tables and their main relationships in this schema would look something like this:

                   

                  1 Cue --< 2 CatalogEntry -- < 3 GenresInCatalogEntry >-- 4 Genres --< 5 SubsAllowedForGenre --< 6 Subs

                   

                  #1, #4 and #6: check; #3 lets you add many genres to a catalog entry, and #5 lets you couple subs with genres.

                  It will also be your conduit into Subgenres to create a conditional value list (or a portal display) showing only the subs that go with the current/selected genre.

                  (You sort of tried this by duplicating a Genre record and giving each “copy” a different sub-genre. Right idea, wrong place …#5 can do this for you, without touching your existing genres.)

                   

                   

                  Second step: many Sub-genres (no field B1, field B2 …). In addition to the schema above, you need either …

                   

                  A: 1 Cue --< 2 CatalogEntry --< 7 SubsInCatalogEntry (directly analogous to #3), or

                   

                  B: 1 Cue --< 2 CatalogEntry --< 3 GenresInCatalogEntry --< 7 SubsForGenresInCatalogEntry

                   

                  The difference being that in A, each sub of the catalog entry “belongs” to the CatalogEntry record itself, while in B it belongs to one of the genres added to the catalog entry.

                  This is also related to the question of how to add only subgenres that “belong” (via the couplings in table #5) to the genre(s) for the catalog entry.

                   

                  For B, you would need to select one of the genres already associated with the CatalogEntry so the new record in #7 can be associated with that genre; for A, you would want to see all subgenres that go with any of the associated genres, but you don't care which one because the new record in #7 belongs to the CatalogEntry.

                   

                  Therefore, A would require a different workflow (and programming/design) than B. The final answer depends on how much information you want and need to preserve (and the granularity of the reports you want to generate).

                   

                   

                  So in total it seems that you need seven tables, either way, where …

                   

                  #1 Cues, #4 Genres and #6 Subs are your main entities;

                  #2 CatalogEntry acts as a join table of those three (with a little help from #3 and #7, regardless of the latter's eventual role) … and

                  #5 is your “rule book” where you state which sub(s) (may) go with what genre(s)

                   

                  Some notes: when you “delete”, say, a Genre from a CatalogEntry, you don't delete the genre itself – you delete a record that constitutes the combination of the Genre from a CatalogEntry, and thus remove the association. An example from RL: If you move out and tear up your rental contract, then that association is no longer valid, or in effect – but both the flat/landlord and you (hopefully) still exist.

                   

                  On the other hand, if you (try to) delete an “actual” Genre, Subgenre, or Cue (a record in the G., S. or C. table), the situation is more complex: has this G/S/C ever been used for a CatalogEntry? Then either delete all those CatalogEntries (plus all its G/S associations), or make it a rule that a G/S/C cannot be deleted once it's been used in a CatalogEntry.

                   

                  Hope this gives you some pointers and helps you to write better soundtracks (yes, I know that the word isn't accurate for what I actually mean …)

                  • 6. Re: Music Cue Database Setup 3 Part Question
                    sgtpeppas

                    Yes! I suppose I lied when I said I had three questions. I would make an excellent politician!

                     

                    I am attempting to assemble something like what you suggested. So far no luck but I'm still working on it and trying to work through the logic and then how to link together with keys and portals. One thing I am unclear on. You said here:

                     

                     

                    First step: many Genres (not as fieldA1, fieldA2 …). The tables and their main relationships in this schema would look something like this:

                     

                    1 Cue --< 2 CatalogEntry -- < 3 GenresInCatalogEntry >-- 4 Genres --< 5 SubsAllowedForGenre --< 6 Subs

                     

                    #1, #4 and #6: check; #3 lets you add many genres to a catalog entry, and #5 lets you couple subs with genres.

                    It will also be your conduit into Subgenres to create a conditional value list (or a portal display) showing only the subs that go with the current/selected genre.

                    (You sort of tried this by duplicating a Genre record and giving each “copy” a different sub-genre. Right idea, wrong place …#5 can do this for you, without touching your existing genres.)

                     

                    It looks like here that no 3 is a Join Table. Where 1 Cue is a parent of 2 CatalogEntry which is joined with 4 Genres by 3 GenresinCatalogEntry. Yet down here you refer to 2 CatalogEntry as a Join Table of 1 Cues, 4 Genres, and 6 Subgenres:

                     

                     

                     

                    So in total it seems that you need seven tables, either way, where …

                     

                    #1 Cues, #4 Genres and #6 Subs are your main entities;

                    #2 CatalogEntry acts as a join table of those three (with a little help from #3 and #7, regardless of the latter's eventual role) … and

                    #5 is your “rule book” where you state which sub(s) (may) go with what genre(s)

                     

                     

                    I am slightly confused on that point

                     

                    In your schema is 2 CatalogEntry a Join Table or 3 GenresInCatalog? Or both?

                     

                    I am absolutely convinced that if I can crack the code and start to see database design as neo came to see the matrix, my soundtracks will see a vast improvement!

                    • 7. Re: Music Cue Database Setup 3 Part Question
                      erolst

                      sgtpeppas wrote:

                      Yes! I suppose I lied when I said I had three questions. I would make an excellent politician!

                      You gotta work on your act!.Part of being a successful politician is not being found out (so soon)!

                      sgtpeppas wrote:

                      I am slightly confused on that point

                       

                      In your schema is 2 CatalogEntry a Join Table or 3 GenresInCatalog? Or both?

                       

                      I am absolutely convinced that if I can crack the code and start to see database design as neo came to see the matrix, my soundtracks will see a vast improvement!

                      It's a join table, because you need to add the two tables from part A|B – Genres, Subs – to the mix. So the full description of a CatalogEntry includes a Cue, and any number of Genres and Subs.

                       

                      When you try to describe an entity and find that to do that you need to add a number of other entities, you have a join table. If the entity is only dependent on one other entity, it's a child table.

                       

                      Run-of-the-mill example: Invoice management. Invoices are children of Customers, because 1 customer - many invoices, but each invoice - 1 customer. InvoiceLineItems, OTOH, is a join table, because you need both an invoice AND a product to describe a line item (plus some “native” data, like qty etc.).

                      sgtpeppas wrote:

                      I am absolutely convinced that if I can crack the code and start to see database design as neo came to see the matrix, my soundtracks will see a vast improvement!

                      Here's hoping!