12 Replies Latest reply on Aug 26, 2013 10:00 PM by chefportal

    Optimal ERD / Design

    chefportal

      Hi All

       

      I am still very new to FileMaker and am stumped for an optimal ERD / Design for a product that can have many variances but is still the same product and stocked by many suppliers.

       

      Eg: Yellow Widget ( Made By Widget Co ) can be purchased 1 x 50g or box of 10 x 50g or 100 x 50g all with their own UPC Number, Also the preferred order qty and supplier can be set.

       

      When you place an order a dropdown list shows with preferred order on top with the other options below.

       

      Eg: I usually order 10 x 50g so the list will show ( 10 x 50g first then 1 x 50g then 100 x 50g)

       

      Any advice would be appreciated

        • 1. Re: Optimal ERD / Design
          Mike_Mitchell

          How do you establish the "preferred" option? Is it based on that user's settings? The number of orders placed against that option? The developer's preference?

           

          Mike

          • 2. Re: Optimal ERD / Design
            chefportal

            Hi Mike,

             

            Preferred option will be setup in product and supplier layouts.

             

            Eg:   When you create a product record you can attach suppliers to it, say "Cheep Widgets Co" and "Supper Cheep Widgets Co" a check box can be clicked on “Cheep Widget Co”, and it will become preferred supplier. I have a handle on that, but not so sure how to setup the product tables. As same products can be purchased in many sizes, I am not sure how to work around this. Should I have a product table with product name and description  info and another with individual product data like the size and UPC number. The product/supplier table will have the price.

            BTW thank you for advice an vanishing field the portal option worked like a treat!

            • 3. Re: Optimal ERD / Design
              Mike_Mitchell

              Just off the top of my head, I would say you would set up a series of join tables. This is a series of many-to-many relationships, so you'll want to have join tables to make those work, basically a join table or tables where you have a record for each unique combination of parent records.

               

              (Huh?)  

               

              In this case, you have a join between Product and Supplier (which you probably already have set up). To answer the other question, according to "pure" relational theory (as if any real-world database ever conformed completely), you would choose to have another join table between Product and Size. Reason: Basic relational theory dictates that you don't duplicate data between tables, and if you tried to keep your sizes in your Product table, you'd have to duplicate the name of the product, price, etc. on each size.

               

              That does, of course, create an issue for your value list. Did I understand you correctly that some sizes are only available from some suppliers? (I think I understood that from the original post, but I'm not sure.)

               

              Mike

              • 4. Re: Optimal ERD / Design
                chefportal

                Hi Mike,

                 

                Constructing an ERD based on your advice, will post it once completed.

                 

                Thanks for your advice, kind of makes sense now.

                 

                Cheers Mark

                • 5. Re: Optimal ERD / Design
                  chefportal

                  I have posted an ERD, is this the sort of setup I need
                  ERD_ProductSupplier.png

                  • 6. Re: Optimal ERD / Design
                    Mike_Mitchell

                    Well ...

                     

                    Your diagram seems to indicate that the supplier is tied to the particular size (what you're terming "Product Variance"). So again, I'll ask the question: Is "Preferred Supplier" merely an indicator of who you'd "prefer" to get it from, or does each supplier only provide a certain set of sizes ("variances")? Because if each supplier doesn't supply all the sizes, you'll need a way to link Supplier with Variance so you can identify which sizes are available from which suppliers.

                     

                    In other words, another join table. Or, alternatively, a table that's a three-way join between Product, Supplier, and Variance.

                     

                    Mike

                    • 7. Re: Optimal ERD / Design
                      chefportal

                      Hi Mike,

                       

                      I will try to make it a little clearer. Product Variance is the same product but different packaging EG: Unit, Inner Packaging, Outer Packaging. Preferred Supplier is the supplier most preferred, but there can be more than one supplier for each product.

                       

                      So…..

                       

                      The scenario is this, a product is created and has the unit details, and preferred supplier attached from a dropdown list. On the same layout a portal is used to add the variances of the product unit.

                       

                      EG: The unit is sold 1 x 50g but is packaged in trays of 50 x 50g and a Ctn is 10 trays making it 500 x 50g units (the variance is used because each packaging has a unique product barcode, so to do a stock take with a barcode scanner it will detect if you scan the Ctn it will show 500 units)

                       

                      When ordering the product the preferred supplier is used, and an order form for that supplier is created. But before the order is create a sub summary report is generated with all the items to be ordered in their respective suppliers at that time the supplier for the product can be changed.

                       

                      I realize this seems a little over kill, but is this scenario the supplier has a minimum spend and when the sub summary is generated the supplier has the cost listed and if it is to low you can choose another supplier with a lower spend limit.

                       

                      I hope this clarifies my situation.

                      • 8. Re: Optimal ERD / Design
                        Mike_Mitchell

                        We need to decide if Product Variance is truly a data entity, or something you do when you're ordering a product. From your description, I can't tell. So the question is: Do you store a library of possible Variances for each product, or do you assemble them at order time?

                         

                        Furthermore, we need to answer the question I asked before: Does every supplier provide every possible Variance?

                         

                        "Preferred Supplier" could be:

                         

                        1) The supplier you prefer for a given product.

                        2) The supplier you prefer for a given variance of a product.

                        3) The suppler you prefer to use for this order when you submit it to Procurement.

                         

                        Which of these is it?

                         

                        These will tell you what your data model needs to look like.

                         

                        Mike

                        • 9. Re: Optimal ERD / Design
                          BruceRobertson

                          I believe the word is product variants, not variance.

                          • 10. Re: Optimal ERD / Design
                            chefportal

                            Hi Mike,

                             

                            Sorry for the late reply had a slight medical problem but back on track now,

                             

                            I’m not 100% sure that the variant should be its own entity, although I think it is. As a product can have many variances in packaging (linked with its own barcode from manufacturer) the unit with barcode 1234 the inner with barcode 4321 and the outer with barcode 1423. all suppliers have the same packaging but some let you order only the unit and others want you to order an outer carton. So with thin information I want to have a preferred supplier with how the product is ordered.

                             

                            I am having trouble with how to link the product with the supplier as the variant. In the product table I have the unit details with size, unit of measure  and single item packaging. In the variant table I have the unit qty in packaging and barcode. So I assume I need to have a supplier/ingredient table that links to the product and its variants with the prices to each of its variants.

                             

                            EG: if you purchase by the unit it costs $1.00 but if you buy a inner of 12 it costs $10.00 and you set the inner as your usual purchasing type so when you do stock take you know it cost $10.00 not $12.00

                             

                            The preferred supplier is for a given product not its packaging.

                             

                            Mark

                            • 11. Re: Optimal ERD / Design
                              Mike_Mitchell

                              Mark -

                               

                              I think you need to break away from what you currently have and think in terms of what you should have. It's clouding your analysis to set your framework around what you have already. (I deal with this all the time; we inherit old applications from people and often have to go "back to formula" to figure out how it should be built in order to get a good data model.)

                               

                              Given that, let's break down what we're actually tracking in terms of entities, ignoring the existing model. Basic data modeling starts with "collect the nouns":

                               

                              Product

                              Size

                              Packaging Option

                              Supplier

                              Order

                              Barcode

                              Price

                              Manufacturer

                              Preferred Supplier

                               

                              Of these, actual candidate entities are probably:

                               

                              Product

                              Packaging Option (or Variant)

                              Supplier

                              Order

                              Manufacturer

                               

                              because Size, Barcode, Price, and Preferred Supplier are, most likely, just data elements attached to one or more of the entities. Size might belong to Variant; Barcode definitely does; Price definitely does; Preferred Supplier most likely (based on your last post) belongs to Supplier.

                               

                              Okay, so far so good. But now we need to figure out how to attach these. Attaching Manufacturer to Product is straightforward; it's a one-to-many relationship (one Manufacturer, many Products). Product to Variant is similar; one Product can have many Variant, but each Variant belongs only to a single Product.

                               

                              Order to Variant is similarly one-to-many (although, in most cases, you'll need to provide a Line Items table for Orders to interpose some control between the Variant and the Order). But each Order is associated with many Line Items, while each Line Item is associated with only a single Order.

                               

                              You mentioned that not all Suppliers provide all Variants. But each Variant may be provided by more than one Supplier, yes? And each Supplier provides more than one Variant (or could). So we have a many-to-many relationship, which should (in most circumstances) be implemented via a join table. Each record in the join table represents a unique combination of Supplier and Variant. In that table, you would include things like Price (because each Supplier charges a specified price for that particular option, as you mentioned in your last post). Similarly, that supplier's barcode for the particular option is unique to that packaging and supplier, so it goes in this table too.

                               

                              Make sense?

                               

                              Mike

                              • 12. Re: Optimal ERD / Design
                                chefportal

                                Hi Mike,

                                 

                                Thanks for the advice, your explnation makes sense just need to get my head around it. Will sit back and re-think what I really need and go from there.

                                 

                                Mark