4 Replies Latest reply on May 18, 2015 8:27 PM by starstuff

    What is the difference between these table relationship

    starstuff

      Title

      What is the difference between these table relationship

      Post

      Hi all!

      i'm testing out filemaker as a suitable replacement for the multiple spreadsheets i use. i'm a beginner in filemaker and relational database in general.

      i'm trying to get my head around these two relationship set-up for category, which is better between the two and why?

      (Tables) are:

      product

      customer

      product_category

      customer_category - retailer, distributor, manufacturer

       

      (Relationships)

      A. category is related to their particular tables

      product is match to product_category

      customer is match to customer_category

       

      B. category is related to the tables by using a separate table

      Here we add a table called CATEGORY,

      the product_category and customer_category would be a join table

       

      product is match to CATEGORY, and match to product_category

      customer is match to CATEGORY, and match to customer_category

       

      which is better of the two?

       

      Thank You for the assistance!

        • 1. Re: What is the difference between these table relationship
          Jade

          I don't know your business model or goals here so take the following with a grain of salt or two.

          The "CATEGORY" may be a red herring.  If you just want to define the customer type (e.g. retail, distributor, manufacturer, etc.), then a tag (value list field) on the CUSTOMER record may well do the trick.  The "CATEGORY" for products may be more complex or dynamic and merit its own Table.  What would be more important IMO is a join table linking CUSTOMERS to PRODUCTS.  In other words:

          CUSTOMER -< Customer_Product >- PRODUCT >- CATEGORY  This assumes a PRODUCT can be assigned to only one CATEGORY

          or

          CUSTOMER -< Customer_Product >- PRODUCT -< Product_Category >- CATEGORY     If the product can be assigned to many CATEGORIES

           

          • 2. Re: What is the difference between these table relationship
            philmodjunk

            Neither is necessarily "better" than the other. Each defines a different type of relationship.

            Example
             

            Table1-----<Table2
            Table1::Table1ID = Table2::Table1ID

            is a "one to many" relationship. One record in Table1 matches to many records in Table2. If you swap ID's around to get:

            Table1::Table2ID = Table2::Table2ID

            You have a "many to one" relationship and it's basically the same type of relationship but now it's a single record in Table 2 that matches to many records in table2.

            But if you set up this system of relationships (called a data model):

            Table1---<Join>-----Table2
            Table1::Table1ID = Join::Table1ID
            Table2::Table2ID = Join::Table2ID

            You now have a "many to many" relationship between table1 and table2. One Record in Table1 can now match to many records in table2 and one record in Table 2 can match to many records in Table1.

            So if a product can be a member of many categories and a category can list many products, this second set of relationships make sense. But if a Product can only be a member of a single category, then the second option is needlessly complex as you can simply use a one to many relationship.

            • 3. Re: What is the difference between these table relationship
              starstuff

              Hi Jade!

              thank you for the breakdown, I will make a custom value list for the customer category, and create a separate table for the product category.

              btw, my goal here is to recreate the idea of dolibarr http://www.dolibarr.org/ into filemaker, im studying its relationships, and how i can apply it to filemaker. is mysql relationship different than filemakers?

              Thank You!yes

              • 4. Re: What is the difference between these table relationship
                starstuff

                Hi PhilModJunk!

                thank you for the great explanation of the table relationships! i had a eureka moment while reading your post about relationships! 

                 

                Thank you!yes