7 Replies Latest reply on Jan 9, 2009 9:16 AM by ninja

    Use repeating fields or related tables for inventory database?

    c1

      Title

      Use repeating fields or related tables for inventory database?

      Post

      Hi, I need to build a new inventory database of products from scratch. Most of the products has different colours which I need to keep track of. Should I duplicate the record and store each new colour as a record on its on? 

       

      I did a search and thought of other possibilities like using repeating fields or related tables.

       

      Any advise will be greatly appreciated.

        • 1. Re: Use repeating fields or related tables for inventory database?
          ninja
            

          Good morning c1,

          Thanks for the post.

           

          Inventory is the primary use for FMP for me.  After playing with different scenarios, I settled on:

           

          Transaction table

          Individual lot# inventory table

          Product table

           

          The product layout has a portal of individual lot#'s of that particular product, linked by product name.

          The Lot# layout has a portal of the transactions for that lot, linked by hidden uniqueID.

           

          Each relationship is a one to many relationship, and the portals help to view this.

          Every product has its own record in the Product table

          Every lot of every product has its own record in the lot# inventory table.

          Every transaction has its own record in the transaction table.

           

          Hope this helps to get the juices flowin'...previous prior planning prevents poor performance.

           

          and the direct answer to your question...IMHO if the customer cares what color they get...then a different color is a different product...make them different records.  It'll save you headaches doing backtracing later.

           

          • 2. Re: Use repeating fields or related tables for inventory database?
            c1
              

            Hi Ninja,

            Thanks for the reply! When I was experimenting, I started making each different product, color a record of its own. That seemed easier to understand for me.

             

            Then I was looking at the database templates and tried the one on invoicing and thats how I come to know about repeating fields. That seems suitable too, wouldn't it?

             

            I can start off quickly using the earlier method of different records of each colour but just want to be sure and not regret not using repeating fields later.

             

            Hope to you can provide more insights to it.

             

            Thanks in advance.

             

            c1 

            • 3. Re: Use repeating fields or related tables for inventory database?
              ninja
                

              C1,

               

              You could make repeating fields work as long as you don't end up with something like 579 repeats in the field.  I personally would recommend against it though (take this as the opinion that it is...I figure its a style thing for the most part).

               

              As you get very comfortable with table relationships, you'll find them to be more powerful and easier to maintain over time.  I personally would use a related table with a one to many relationship rather than a repeating field.  Perhaps I simply understand related tables better than I understand repeating fields.

               

              I run two main dbases for inventory (Raw materials & Finished Goods).  I have ~4000 finished goods with ~6000 individual lots on record at any given time, and 2000 raw materials with ~2000 lots...total of ~30,000-60,000 transactions per year.

              I do not have a single repeating field...can you imagine a repeating field with 2000 repetitions...gives me the shivers.

               

              Again, it may be more of a style thing, and it would certainly be affected by how much info you need in the field...I can see how repeating fields might be a benefit...but I can't see how they would offer what I cannot also get by the related table.

               

              My opinion, go with the related tables.  Once you're comfortable with them, I don't think you'll regret the choice.

              • 4. Re: Use repeating fields or related tables for inventory database?
                swj
                   Please, please DO NOT use repeating fields. Even if you can get them to do what you want (which I doubt), at some time you WILL be sorry!
                • 5. Re: Use repeating fields or related tables for inventory database?
                  c1
                    

                  Ninja and Scott,

                  Thanks for the advice! I will go ahead without using repeating fields.

                   

                  What about database for managing invoices, should I not use repeating fields as well?

                   

                  • 6. Re: Use repeating fields or related tables for inventory database?
                    swj
                      

                    Same answer. NO!

                     

                    You never know how many repeats you will need. As soon as you set it to 10, you'll need 12. And there are issues with finding and sorting repeating fields. 

                     

                    Just make an "Invoice Lines" table with an invoice_number field that you can use to link to the Invoice table. No limitations, lots of flexibility, no regrets.

                    • 7. Re: Use repeating fields or related tables for inventory database?
                      ninja
                        

                      Thanks for the agreement Scott,

                       

                      c1, just like each product has many lot# records in another table...

                      and just like each lot# has many transactions in another table

                       

                      so should your invoice have many line items in another table.

                       

                      Repeating fields...I wouldn't, I haven't, I won't.  I'm sure they have a good use, I'll trust the FMP folks to have put them in for a good reason...but I haven't found that reason yet.