9 Replies Latest reply on Jun 26, 2017 3:32 PM by bigtom

    Database Structure Overview Question


      I am a Beginning/Intermediate FileMaker User/developer. I have built some very simple invoicing databases that have the standard tables...


      - Customer Table

      - Invoice Table (withy portal that holds Line Items)

      - Line Items Table (Where prices are generated by inputting information about costs etc.)


      .... But What if I want to go 1 level deeper. What if I wanted there to be several potential SEPARATE tables that can be drawn upon in order to create the price for any 1 line Item on the invoice. Sometimes it might be information from just 1 of the potential "child" tables that feeds into the Parent Line Item, and sometimes it might be a combination of multiple amounts from Multiple "Child" tables in order to come up with the single price for the Parent Line Item, where that "Parent Line Item" is just 1 line item related to an Invoice which will have several line items in it's portal.

          How should I think about this from a ERD Standpoint?

      - Is it legitimate to have a Row of a Portal on an invoice look into at Table that is actually just a combination of potentially several tables?

        I have attached a VERY rough ERD of what I am trying to ask about. Is this a reasonable way to do it, or am I thinking about it wrong? Don't pay attention to any of the specifics of the ERD tags as they are just placeholders. The only things that are relevent in the ERD are the Table Names and the Linking lines of "one to many."

        Thank you for your assistance here.

        • 1. Re: Database Structure Overview Question

          Are you looking for set multi level pricing? I would use a pricing table as a child to the item table and lookup from there. I suppose there are a few ways to do this.

          • 2. Re: Database Structure Overview Question

            You could have a JOIN table between that has

            PRODUCTS - product_prices

            This join table could have several "foreign key" fields (one to each 'other' table).


            I'm not entirely sure why you need to do this, but the "join" between would certainly help. What you are then selecting is the PK from the "product_prices" to tunnel the price through to "PRODUCTS" (which can also be used in the invoice items). You see how complex it is? So again, let's re-think...




            • 3. Re: Database Structure Overview Question

              1. are you using it just to practice FMP or do you/your client really need that solution?


              in the last case: you need to add ORDERS. because there is accounting wise (think stocks, payments etc) a big difference between orders and invoices.


              2. what you need is a "recipe-table" (doesn't matter if you talk about powders or computer parts). imagine an online computer shop. you order a laptop. the shop assembles the laptop according to the "recipe" or "assembly" table (standard model) plus your extra's.

              so in the recipe table you list all parts, as far as people can order them separately, plus any assembled laptop that you sell. accounting-wise (you will see the same often in invoices from suppliers), you get order- confirmations will a whole list from the recipe/assembly table which lists every part.

              • 4. Re: Database Structure Overview Question

                    As a further explanation, First of all, thank you all so very much for you replies.


                Big Tom:

                   Each of my line items is, for all practical purposes, unique, so a look-up table, I don't think is a viable way to do this one.



                   Ya you are correct, it does get very complex and that is why I am wondering if there is a better "big picture" way to do this ;-)  And your solution of having a join with several foreign keys is the way that I have been attacking it. But as you say, it looks like it will be getting very complex.



                    Your case explaining the computer shop is interesting, but not sure it can account for all the things that I need to, I will think more on that. The only thing with the recipe table is that each "ingredient" for me may or may not need more production onto it before being put into the final product.


                I am building this system for my own business, so I am the self developer. I have built a Filemaker Invoice Solution that I have been using over 15 years. In much simpler form. First version long ago had repeating fields none the less. Now I would like to re-build from scratch to make is much better.



                So possibly as a deeper explanation...

                   Any line item can be very simply or massively complex... For example.

                   I purchase raw product and then I put work into that product. So the table that I go to in order to develop the customer prices might start with a simple "Cost Plus" Scenario, where I enter the cost of goods, then mark that cost of goods up a certain amount. Then, there are several other costs that I want to put into that product "line item" for the labor put into that base material, and then other materials added to that original material. And then there may be one or more flat rates to put onto each line item based upon minimums and such.

                  Sometimes I might want all of that workup information to be in the line item description and sometimes not. It can depend on the project. But suffice it to say that each line item is essentially unique. While there may be times when a line item is the same as some other invoice, for the most part they are all unique because of the vast amount of custom manufacturing that can go on. So a table listing all of the possibilities and then looking up is not viable here.


                  Any other thoughts?

                • 5. Re: Database Structure Overview Question

                  This is just BOM where you have "assemblies" as a single price and that is what is shown on the invoice. However what the buyer sees and what you must pay for the materials, labor, service, etc. is typically what your accountant may need to see.


                  So create an "assemblies" table (a join) that may be several components combined into one price. With the relationships being two-way, you can always get back to a full breakdown of the components if ever needed.



                  • 6. Re: Database Structure Overview Question


                       Thank you again so much for your feedback here. I think that I  understand you and will try that. But what do you mean BOM?




                    • 7. Re: Database Structure Overview Question

                      Bill of Materials, which can be a 'summary' (single price/line item per assembly), but the Cost Accountants want to see every component as a line item.


                      When I write quotes, these typically are assemblies. The final invoice may be assemblies or components (depends on customer). But the final BOM is components.


                      You may search for BOM on this forum for many suggestions.

                      (not to be confused with encoding text that may have a byte-order-mark)


                      • 8. Re: Database Structure Overview Question


                            Got it. Thank you very much....!!!




                        • 9. Re: Database Structure Overview Question

                          I would also advise having a stock qty field even if it is a unique item system and the value is always either 1 or 0. I have worked with companies that deal with unique products and they generally have the same thoughts on not needing a stock field, but eventually they see the benefits of a qty on hand.