7 Replies Latest reply on Jan 25, 2010 1:50 AM by comment_1

    Help on how to make a relational database

    asb26

      Title

      Help on how to make a relational database

      Post

      I have used FileMaker a few times before to make custom databases from scratch for a few different things but they have been what I think you call "flat" databases. I now need to make a relational one and I am really struggling. I have tried to self-teach myself and but haven't got any further than learnt how to create relationships between tables.

       

      What I am trying to do is make a database for a list of artworks but then have it so once you enter/select the artist's name it brings up or goes to the details on that artist - so the info on the artist is entered once but might be used by a number of records.

       

      I am using FileMaker Pro 9 on Windows Vista.

       

      Thanks so much.

        • 1. Re: Help on how to make a relational database
          mrvodka
             I would read up relational theory first to get a basic understanding. In your example, there would be at least 2 tables. One to store the artist information and the other the artwork. YOu would relate them together based on ArtistID.
          • 2. Re: Help on how to make a relational database
            ninja
              

            Howdy asb26,

             

            You would need a field in your ARTWORK table for ArtistID along with things pertinent to the artwork (size, media, etc.).

            You would then make a second table of ARTISTS which would have ArtistID and things pertinent to the artist (name, DOB, etc.)

             

            Link the two tables together by File>Manage>Database>relationship tab dragging ARTWORK::ArtistID to ARTIST::ArtistID.

             

            On your layout based on ARTWORK table, put in the field ARTWORK::ArtistID.

            Also put on the artist's name, DOB etc. from the ARTIST table (these should start with "::" when viewed in Layout Mode).

             

            When you type the ARTWORK::ArtistID into the field, and there is a matching ARTIST::ArtistID in the ARTIST table, a link is formed which will populate the name and DOB fields on your layout.

             

            Note that the data will not actually move, the Database will simply know what data to show since you have matching values in ARTWORK::ArtistID and ARTIST::ArtistID.

             

            Get started on that.  When you've gotten that far, we can chat about how to conveniently populate the ARTWORK::ArtistID field using a value list so you don't have to memorize all of the ID numbers.

             

            PLEASE NOTE:

            ARTIST::ArtistID should be an autoenter serial#.

            ARTWORK::ArtworkID should also exist and be an autoenter serial#.

             

            Let us know when you're this far...then we'll go farther. ;)

            • 3. Re: Help on how to make a relational database
              RickWhitelaw
                

              Ninja wrote:

               

              PLEASE NOTE:

              ARTIST::ArtistID should be an autoenter serial#.

              ARTWORK::ArtworkID should also exist and be an autoenter serial#.

               

               

              To expand, ArtistID would be an auto-entered serial in the "parent" table only(ARTISTS). In the child table (ARTWORK) it would be a simple text or number field. Allow creation of records in the Artwork table (from the Artist Table) would be enabled. The Artwork::ArtworkID is something else (not a match field in this situation) and as Ninja says, should be auto-entered serial. This will be useful, as an example, if you set up a table for notes or descriptions of a piece of art.

              I'm making the assumption you will be creating records of ARTWORK from a table of ARTISTS. 

              • 4. Re: Help on how to make a relational database
                asb26
                  

                Thanks so much to you all for your help. I have now managed to do it but why does ArtistID need to be an auto-serial number? I have it is just text and it seems to work fine -- although could this be problematic? A number seems a bit odd as how would one ever remember what number to use.

                • 5. Re: Help on how to make a relational database
                  RickWhitelaw
                    

                  "A number seems a bit odd as how would one ever remember what number to use."

                   

                  Nobody ever has to remember this number. When creating records in the "child" table FM automatically IDs these records with the same number. You don't have to know it, and you never have to see it if you don't put it on a layout. Sometimes people DO put this field on a layout temporarily until they're certain everything is working, but after that there's no point. The key fields, be they primary or foreign, can safely be kept out of sight of the user.

                   

                  RW 

                  • 6. Re: Help on how to make a relational database
                    ninja
                      

                    asb26 wrote:

                     A number seems a bit odd as how would one ever remember what number to use.


                    Rick is correct.  It is general good practice for your ID#s to be meaningless except for linking things.

                    You would never have to see it or know it...but your relationship would rely on it outside of human interference.

                     

                    If you wanted to add a painting to your ARTWORK table and link it to an artist from the ARTIST table, you would use a value list.

                    Said value list would be based off of the ARTIST::ArtistID field, but you would then check "Use only values..." from a second field like "ArtistName".

                    In that way, you would select the artist by name, but the ID field would get the correct serial#.  Utility is thus easy, and your ID#'s are doing their job behind the scenes.  You wouldn't have to remember anything beyond ArtistName, and the Dbase will have no trouble differentiating between all of the Mark Smith's of the world...they'd each have a different ID#.


                    • 7. Re: Help on how to make a relational database
                      comment_1
                        

                      Ninja wrote:
                      you would then check "Use only values..." from a second field like "ArtistName".
                      ...

                      the Dbase will have no trouble differentiating between all of the Mark Smith's of the world...they'd each have a different ID#.


                      If you select 'Show values only from second field', duplicate names will show as a single entry. Anyway, if you have duplicate names, you wouldn't want to use name alone as the "second field", since the user wouldn't be able to tell them apart even with the serial ID showing.