3 Replies Latest reply on Sep 2, 2014 3:45 PM by philmodjunk

    Museum Collection Solution Question

    ewukitsch

      Title

      Museum Collection Solution Question

      Post

      Good morning,

       

      I am a very basic FileMaker user who is learning as I go.  I have been directed to accomplish a complex task and I could use some expert advice.

      I am creating a museum collection database for an automotive museum.  This database will cover vehicles, photos, artifacts and film/video.  My initial approach has been to create separate tables for each item classification (Vehicles, 2D Artifacts, 3D Artifacts, Photography, Film and Video).  At the moment, none of these tables are relational, and since I have been directed to not use this database to track exhibits, I am struggling to find a relationship between them all.

      One of the relationship ideas I have revolves around using the catalog number to link a master item list to the separate tables.  Now, the difficult part.  Different category has a letter based prefix describing the category and a numeric number (or series of numbers) tracking the item.  This would look something like VEH.00001 for the first vehicle entered into the table, and VEH.00025 for the twenty-fifth.  I would like the number to be generated and controlled by Filemaker and show up in a master table, but I am not quite sure how to achieve this.

      So, my question(s) to the community are thus:  Am I going about this the right way, with multiple 'flat' tables?  Is there an easy way to achieve a complex numbering system?  Should I be looking at scripting for what I am attempting to achieve?

      Thank you!

      Erin

        • 1. Re: Museum Collection Solution Question
          philmodjunk

          I have been directed to not use this database to track exhibits

          That is a very odd and very unfortunate directive. Any time that the end user starts telling the developer how to design the database (instead of focusing only on what the database will do for them), things get difficult as they often do not understand what they are telling you to do.

          "Do I put this data into separate tables or all in one big table" is one of important database design questions for which there often is no hard and fast rule to tell you which option to use.  What you can do falls on a spectrum of choices with two extemes: 1 extreme is all separate tables like you have here. The other extreme is one big table with fields that are left empty when not relevant to a particular record in the table The more the data in each table is the same, the stronger the argument for putting it all in one table. The more each table stores data that is different, the more it makes sense to use different tables for each.

          But usually the best answer is somewhere in between. You set up a single unified table for all items where you set up fields for each field that is needed for every item in your collection, at least a catalog number and a description field, but you may have others such as a date received or a location field and then you link in your current tables as "detail" records that link to a primary key ( which may not be your catalog number) in the unified table.

          • 2. Re: Museum Collection Solution Question
            ewukitsch

            Thank you for your response!  I've been thinking that the unified table route was the way to go and it is good to get verification of that.  Have you, or anyone else for that matter, thoughts on complex, auto generating numbering systems in Filemaker?

            • 3. Re: Museum Collection Solution Question
              philmodjunk

              My thoughts are not to use them except as a "label" to be displayed on a computer screen or to print out and attach to the item. Do not, under any circumstances, use such a field as the primary key to link one table to another. Stick with an auto-entered serial number or Get ( UUID ) in an auto-enter calculation as the way to link your unified table of items to other tables in your database.