11 Replies Latest reply on Jun 3, 2015 8:32 PM by starstuff

    Are the tables and relationships correct?

    starstuff

      Title

      Are the tables and relationships correct?

      Post

      Hi All!

       

      i redid the whole table and relationship, i would like to ask some advice if i'm on the right track or not so i could redo it again.

      this is for a sales invoice with customer product price record and purchase invoice with supplier product price record

      Tables:

      (GREEN)

      ThirdParty - record of supplier and customers

      ThirdParty_Contact - record of supplier contacts and customers contacts

      (RED)

      Product - record of product

      Product 2, 3, 4 - TO of Product

      (ORANGE)

      LineItem_Sales - record of customer invoice and product

      Invoice_Sales - record of customer invoice and thirdparty (customer)

      ProductPrice_Customer - record of thirdparty (customer) and product

      (BLUE)

      LineItem_Purchase - record of supplier invoice and product

      Invoice_Purchase - record of supplier invoice and thirdparty (supplier)

      ProductPrice_Supplier - record of thirdparty (supplier) and product

       

      Some concerns

      1. Should i separate the ThirdParty table into 2 ( customer table and supplier table), i combined them due to some customer might be a supplier and vice versa?

      2. Should i combine the table LineItems, Invoices and ProductPrice? i've been thinking of combining these into one because they almost have the same fields.

      3. I have 4 TO of Product but would like to merge it into one Product table if its possible, i noticed that if i start on the Product going to ThirdParty, the ThirdParty table needs to be a TO, and vice versa. i started with table ThirdParty so i got Product TOs,

      Could i simpfily this to have just one Product and one ThirdParty Tables?

       

      Thank You All!

       

       

      relationships2.png

        • 1. Re: Are the tables and relationships correct?
          SteveMartino

          What's the purpose of the ProductPrice_Supplier, Product 4, and ProductPrice_Customer , Product?

          I also don't understand the ThirdParty TOs

          Can Suppliers also be customers? 

          If it were me, I would either go with the Selector/Connector Model, or I would separate this into 2 anchor/bouys.

          One would be customer, Invoice, lineItems, Products

          Supplier, Purchase, LineItems, Products. 

          The fields that need to be filled in on either line item table are usually by calculation or lookup from their respective products and customer tables.  So I wouldn't combine the TO's, then you'll never have a historical record of transactions.  Everytime you change something (price for a product), it will change on the historical records, if you don't have a line items table separate from products.

          • 2. Re: Are the tables and relationships correct?
            philmodjunk

            1. keep them in the same table and for the reasons you've indicated. If data on one of these contacts needs to be changed, you don't want to be in a situation where you have to find the same copy of the data in two different tables and update them identically.

            2. No they record very different data. Products are your list of products that you buy or sell. Line items documents the sale or purchase of a single product on a specific order. ProductPrice appears to be here in order to document a specific price for a specific product purchased from a specific supplier or sold to a specific customer. You only need this table if you have different prices for the same product for different customers or different suppliers.

            3. Sorry but you already have only one product table, you have 4 different ways to refer to records in that table, your 4 TO's. You need them for your different relationships.

            What you might do is merge the two line items tables, with one field for Qty purchased and a different field for Qty sold. This would allow you to use this table as a kind of "inventory ledger" where a summary report can both tell you the total on hand for each product and also how your levels rise and fall over time, which can help you better manage the re-ordering process.

            For more on an "inventory ledger", see: Managing Inventory using a Transactions Ledger

            You might also find this article an "anchor buoy" helpful: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

            • 3. Re: Are the tables and relationships correct?
              starstuff

              @STEVE MARTINO

              Hi Steve,

              What's the purpose of the ProductPrice_Supplier, Product 4, and ProductPrice_Customer , Product?

              The purpose of the ProductPrice_Supplier and  Product 4 is for recording the price per product of a supplier

              Any supplier can carry more than one product in the Product 4 table, and each of them have their of price per product

              Example: SupplierA and SupplierB both carry Apples

              SupplierA price for Apples is $25

              SupplierB price for Apples is $23

              Both prices needs to be recorded incase SupplierB run out of Apples, one can negotiate SupplierA for $23 or $24

               

              The purpose of the ProductPrice_Customer and  Product  is for recording the price per product of a customer

              Any customer can buy a product in Product Table, and each customer can have their own price per product, more or less similar to the supplier.

               

              Example: CustomerA and CustomerB bought Bananas

              CustomerA Bananas sold at $10

              CustomerB Bananas sold at $15

              Next time CustomerB order Bananas, CustomerB will be prices at $15 not $10

               

               

              I also don't understand the ThirdParty TOs

              They are not TOs, its 2 tables ThirdParty and ThirdParty_Contacts, i colored it based on the groupings i understand

              ThirdParty combined the suppliers and customer name, mostly company name

              ThirdParty_Contacts, here i will add any contact detail, mostly person in charge

               

              Can Suppliers also be customers? 

              yes, it happens, sometimes you carry products that they want/need.

               

              If it were me, I would either go with the Selector/Connector Model, or I would separate this into 2 anchor/bouys.

              One would be customer, Invoice, lineItems, Products

              Supplier, Purchase, LineItems, Products. 

              The fields that need to be filled in on either line item table are usually by calculation or lookup from their respective products and customer tables.  So I wouldn't combine the TO's, then you'll never have a historical record of transactions.  Everytime you change something (price for a product), it will change on the historical records, if you don't have a line items table separate from products.

              thank you for the tips, i will study these techniques

              • 4. Re: Are the tables and relationships correct?
                starstuff

                @PhilModJunk

                Thank you very much for the help!

                1. Will retain as is

                2. Will retain as is,Product and Product Prices (i need to track product prices), except for the LineItem, these will be merged

                3. You're right, i only have one Product Table.

                Will study the inventory ledger and anchor-bouy! thank you very much again for the help!

                • 5. Re: Are the tables and relationships correct?
                  starstuff

                  Hi again!

                  i've update the relationship based on the suggestions, is this relationship better than the first one? or not?

                   

                  @PhilModJunk

                  i've also read about anchor-bouy, i don't quite get it yet, one of the things i don't understand is whats the basis of "context"?

                  does it mean if they are related in a context, i will create another TO?

                  in the example here http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

                  if im going to create a SUPPLIER anchor, it will be almost the same as CUS_CUSTOMER, is that right?

                   

                  Thanks All!

                   

                   

                  • 6. Re: Are the tables and relationships correct?
                    philmodjunk

                    "Context" is the table occurrence that you select for the layout in "show records from". This establishes the layout's context as it will determine what data can be accessed from related tables.

                    Every layout that the user sees should be based an an "anchor" table occurrence and not a "bouy". The table occurrences specified for portals and fields from related tables will come from "buoys" connected to the layout's "anchor". This tends to divide up your relationship graph into smaller, simpler groups of table occurrences each designed to support a different basic task or function of your database and that in turn usually produces a relationships graph that is simpler and easier to work with.

                    • 7. Re: Are the tables and relationships correct?
                      starstuff

                      Hi PhilModJunk!

                      Thanks for the great explanation, i sort of got it now. 

                      Say in the updated relationship above, i can remove the ThirdParty_Contact Table in the Product Table because i don't need the ThirdParty_Contact while looking at a product.

                      Also, if that is the case, as long as there is a connection in any TOs then one can get see a record from any table? is that correct?

                       

                      Thanks!

                      • 8. Re: Are the tables and relationships correct?
                        philmodjunk

                        But not just any record. The relationship, the layout's current record and the values in the relationship specified match fields will control what record is accessible/visible. And if there is more than one related record, only the first related record is generally accessible unless you use a special function or a portal. And when you get a "chain" of TO's linked in a relationship, you can't reference records at the end of the chain unless the relationships that form each "link" are valid for the current record on the layout based on the first record of the chain. (And long chains can have update/performance issues due to all the processing needed from FileMaker in order to "tunnel" through the intervening tables to get to the data.)

                        This does touch on what I think of as "anchor buoy II"--normally called "Selector Connector". This is an enhanced version of Anchor Buoy co-developed by SeedCode and GeistInteractive that links each Anchor into a larger structure that makes it easier to set up interface structures that can be copy/pasted from layout to layout even when the layout's context is a different table occurrence group.

                        These are concepts that I suggest that you not visit until you are comfortable with Anchor Buoy.

                        • 9. Re: Are the tables and relationships correct?
                          starstuff

                          Hi PhilModJunk!

                           

                          i recreated the design of the database again, is it better? Thanks

                           

                          Tables:

                          Product (TOs Products_invoices)

                          Line_Items (TOs, Line_items_PO, Line_Items_SO)

                          Invoices_PO (purchase order)

                          Invoices_SO (sales order)

                          Companies (TOs Companies_Invoices_SO, Companies_Invoices_PO)

                           

                          Questions

                          1. Based on the suggestion before to combine the Line_Items into 1, does TOs can be considered as 1 table? 

                          I did add 2 new foreign keys, the _fk_invoice_so_id and _fk_invoice_po_id. Should i combine these into 1 _fk_invoice_id? or better to separate them like what i did?

                           

                          2. Another question is how to separate the suppliers from the customer in the Companies table, you can see below,

                          i added a companytype field to Invoice Table, its a calculated field with "Customer" or "Supplier" text,

                          this will be a checked box, so user can select 2 answer, incase the customer is also a supplier or vice versa.

                          is this the correct design? or not?

                           

                          Thank you again!!

                           

                          • 10. Re: Are the tables and relationships correct?
                            philmodjunk

                            1. TOs are not tables. Each TO is a reference to a specific table. You can set up many references (TOs) to the same table. FileMaker's developer interface makes this confusing as most references to "Table" inside FileMaker are really referring to a table occurrence--one of the "boxes" in your table occurrence graph rather than one of the Tables in Manage | Database | Tables.

                            Since both _fk_invoice_po_id and _fk_invoice_so_id match to the same exact _pk_invoice_po_id, you could use just one _fk field here. But it's really going to depend on the rest of your database design. The different _fk fields will distinguish Sale and Purchase line items from each other, but if, on a "ledger layout" intended to show both sales and purchases at the same time, you need to show data from invoices, the different _fk fields will complicate your layout design.

                            2. It depends on how you need to use that relationship. For listing company information on an invoice layout, the added match field is not needed. But with it, you can set up a conditional value list or selection portal that automatically limits to one category of company record or the other--which can be useful.

                            • 11. Re: Are the tables and relationships correct?
                              starstuff

                              Thanks a lot PhilModJunk! i think im ready to build this design.