6 Replies Latest reply on Dec 1, 2009 12:54 PM by hpol

    Help figuring out tables

    hpol

      Title

      Help figuring out tables & relationships needed for planned DB

      Post

      I'm using FileMaker 10 Advanced in Windows XP environment that will be shared over the network or web (not sure yet).  I am a beginner, maybe adv. beginner.  I worked on developing FMP solutions for various artists to handle their artwork and an organization about 5 years ago - but have forgotten tons & am attempting something much more complex.

       

      I'd like to create a database to manage information about video games: Game, platforms, studio that made the particular game (game + platform=specific game), developer, publisher, people associated with the game & their role.

       

      Tables I created:

      Game

      Platform

      Game_Platform

      Studio

      Contacts

      Studio_Contacts

       

      I figured I would need join tables and created two of them - but now it seems I'll need to join the joins(Game_Platform & Studio_Contacts) so that I can have a Layout that would look like this:

       

      Game: Batman: Akham Asylum    Platform: XBOX360

      Series: Batman

      Released: 08/25/09

      Developer: Rocksteady

      Created by:  Rocksteady (sometimes different from developer)

      Publisher: Eidos

      Genre: Action Adventure

      Studio Director: Jaime Walker

      Art Director:  Dave Hego

      Studio Producers:


      I'd like to have layout for the Game that has each platform listed in a portal.  I'd like the release date, created by, and platform information to show & also be able to access the full layout by clicking the row in the portal.

       

      I'd like the contact page to be able to listthe games associated with them.  I'd like to look at all the games published by a company.

       

      Do I need another table that would connect Game_Platform & Contacts/Studio?  

       

      Not sure of the best way to set this up & have been looking for relationship tutorials/advice.  Hopefully someone can help me out?  I'd super appreciate it!

       

      THANKS!!!!

        • 1. Re: Help figuring out tables & relationships needed for planned DB
          philmodjunk
            

          "I'd like to have layout for the Game that has each platform listed in a portal.  I'd like the release date, created by, and platform information to show & also be able to access the full layout by clicking the row in the portal."

          That part's pretty straight forward if your portal is based on Game Platform. Since Filemaker's added a few new features to Go To Related record over the last 5 years and did a terrible job of documenting the changes, see the following link for more on Go To Related Record--paying close attention to how GTRR behaves when triggered from a portal:

           

          The Complete Go To Related Record

           

          As to the rest, I have questions where your answers should point the way:

           

          For a given Game Platform record, will there be just one Studio or mutiple studios?

          If just one studio, then you may need just a Studio ID field with the appropriate links to your studio table.

           

          For a given Game will there be different studios for different platforms of the same? If not, then you need a link from your Game table instead of Game Platform. If there can be multiple studios for a given game (but the same for all platforms) then you need a Game Studio Join table.

           

          See if that helps you out.

          • 2. Re: Help figuring out tables & relationships needed for planned DB
            hpol
              

            Thank you very much for your time & info!!

             

            I'll check out the GTRR stuff now.

             

            As for your other question/answers...maybe I could ask you for a little more help?  (sorry)

             

            For a given Game Platform record, will there be just one Studio or mutiple studios?

            If just one studio, then you may need just a Studio ID field with the appropriate links to your studio table. 

             

            There will be more than one studio associated with a Game Platform, although I may be able to figure out a way to do it.  Right now, all companies (publishers, creators, developers) are in one Table.  Would some sort of calculation script be useful to separate out the three roles?  I'll stop suggesting possibilities so I don't muddy the question too much.

             

            For a given Game will there be different studios for different platforms of the same? If not, then you need a link from your Game table instead of Game Platform. If there can be multiple studios for a given game (but the same for all platforms) then you need a Game Studio Join table.

             

            I'm a little confused - there will often be different studios for different platforms of a game.

            Example:

             

            Tomb Raider Underworld developed by Crystal Dynamics

            TR:U XBOX360 & PS3 were created by Crystal Dynamics

            TR:U Wii & PSP were created by Buzz Monkey

             

            GAME developedby STUDIO1

             

            GAME_Platform1  createdby STUDIO1

            GAME_Platform2  createdby STUDIO1 

            GAME_Platform3  createdby STUDIO2

            GAME_Platform4  createdby STUDIO2 

             

            THANKS!!

            • 3. Re: Help figuring out tables & relationships needed for planned DB
              philmodjunk
                

              Well that clears things up a bit but is this ever possible?

               

              GAME_Platform1  createdby STUDIO1 AND STUDIO2

               

              IF so, then you need a Join table to Join Studio records to Game Platform records.

               

              If not, then a single field in Game Platform that identifies the studio should work. Assuming your Studios table contains a serial ID number field, I'd put a matching number field in Game Platform.

               

              "Would some sort of calculation script be useful to separate out the three roles?"

              I don't see any need for that given what you describe. A simple data field can be used to label the role or even list the roles if more than one is possible.

              • 4. Re: Help figuring out tables & relationships needed for planned DB
                hpol
                  

                Thanks again....

                 

                This can happen:

                 

                GAME_Platform1  createdby STUDIO1 AND STUDIO2

                 

                 

                IF so, then you need a Join table to Join Studio records to Game Platform records

                 

                ...gets back to my initial question...and which makes me feel like I'm not creating tables as I should. 

                I would need to create a Join table between two already exisiting join tables.

                Game_Platform and Studio_Contact

                 

                Here are some thoughts:

                Maybe I don't need three tables of Game, Platform, Game_Platform table.

                I just need a Platform table with a dropdown FIELD with the game name. 

                The Platform table only contains the roughly 10 platforms that exist and a GAMEID to link it to Game.

                 

                So, it is sort of like editions of an art print.  There are 5 versions of one print.  I would create 5 records for edition 1-5.  I would not create a record for the image of the print because I could get the information about the image from the individual record.  It is frustrating to me that I can't figure this out!  I know I created a db for a an artist with this issue, but I thought I DID create a record for the image and then 5 records for each edition.  Does that make sense?

                 

                 

                Thanks again for your help.

                 

                • 5. Re: Help figuring out tables & relationships needed for planned DB
                  comment_1
                    

                  You neglected to say what do you expect to achieve by managing all this data. Perhaps all you need is the 4 basic tables:

                   

                  Games

                  Platforms

                  Contacts

                  Studios

                   

                  and one star-join table of Roles, where each record would say that Contact X worked on Game Y in the capacity of Y for Platform Z, on behalf of Studio U.

                   

                  • 6. Re: Help figuring out tables & relationships needed for planned DB
                    hpol
                      

                    You have a point....a good point.  I often complicate things more than I need to.  I'll give the simple solution a shot and see how it goes.  Hopefully I'll come back to the forum to tick this off as SOLVED.

                     

                    Many thanks to all!