4 Replies Latest reply on Jan 10, 2010 12:40 PM by QuickRon

    Need help w/ auto parts database model



      Need help w/ auto parts database model


      I'm new to FileMaker and need to create a database to manage auto parts. The biggest challenge I'm facing is how to best create the tables & relationships to manage the "applications" of the parts.


      The main Product table will contain the data that is unique to each part (ie. part number, price, weight, image, etc.). But the "application" is different. One part number (or product) can fit different Years of different Makes and Models.


      "Part234" can fit:
      1987-2000 Chevy Tahoe
      1987-2002 GMC Yukon
      1986-1999 Chevy Suburban


      So, what kind of table structure (& relationships) would I need to create to accommodate this kind of application data? Do I create a table for "Makes" and a different table for "Models"? Also, the Product and Makes tables would be Many-to-Many (1 product can fit many Makes and 1 Make can fit many products), so how would I set that up?


      And how would I handle "Years"? If the format is "1987-2000 Chevy Tahoe", do I have a "Starting Year" and "Ending Year" fields? Do I have a table of just single years?


      Thank you,

        • 1. Re: Need help w/ auto parts database model

          I would use two tables related by part number. The table for "Make" would contain fields for: part number calculation, beginning year, ending year, make, and model. Each field would have a pop-up list of previously used choices.


          In a portal on the parts layout, it would show those various fields.  

          • 2. Re: Need help w/ auto parts database model

            Hi TECman.

            Thank you for your quick response.


            I think I understand what you're saying. But what do you mean by a field for "part number calculation"? What is the calculation you're referring to?


            And when you say "pop-up list" you mean a list with pre-populated values, where I can select multiple values at once?


            What I don't understand is ... if the Portal will show the various fields in proper sequence, how will it know which values (ie. make, model, beginning year, etc.) go together? How will it know to layout the values correctly so that it shows, for example, "1987-2000 Chevy Tahoe" and not something else?


            Thank you,


            • 3. Re: Need help w/ auto parts database model

              When I mentioned a field for part number calculation I was referring to the creation of a new portal record which would have an auto calculation for part number that would serve as the needed relationship.


              The pop-up list would be used for the various fields where you would select one choice based on previously entered values for that field rather than a set list. Example: for the beginning year the first time you create a record there would be nothing in the pop-up list. You type in 2000 for that record. The next record you type in would should 2000 as a choice and not wanting that year, you would type in 2003. Third new record would give you 2 choices, and so on.


              On second thought concerning model names, you should be using another related table. Look up Conditional Value Lists for that one.


              You should be then be formatting the portal showing 3 fields from the make table and one from the new model table ... Make::Beginning Year then Make::Ending Year then Make::Name and finally Model::ModelName.


              Read up on Conditional Value List and it may become clearer to you how to structure this.  

              • 4. Re: Need help w/ auto parts database model

                I'll do some reading up on Conditional Value List as you suggest.


                Thanks again for your input.