1 Reply Latest reply on Aug 17, 2011 9:26 AM by philmodjunk

    Multiple Relationships between Two Tables?

    newbievin

      Title

      Multiple Relationships between Two Tables?

      Post

      Hi,

      Filemaker newbie here looking for some advice on how to design a database based on the kind of data I'm dealing with.

      I have multiple brands where most brands have only 1 sub-brand of the same name, but some brands have multiple sub-brands with different names. In some cases, the sub-brand name is the actual brand name itself.

      Example of the data I have: 

      Brand ABC has ONLY one sub-brand named ABC.
      Brand XYZ has MULTIPLE sub-brands named DEF, GHI, KLM, and XYZ(sub-brand name=brand name).

      My Brand/Sub-Brand table looks like the following. To avoid redundancy, I would like to have ONE table that consists of both brand data and sub-brand data.

      I would like to design a inventory table where I can keep track of inventory of products under all sub-brands, but be able to identify the parent-brand of each product, as well.

      My problem with the following design is that I cannot query the SubBrand Name, when fkBrand is NOT equal to fkSubBrand. What is the best way to build a relationship so the inventory table can query the proper data?

      Thanks in advance, for all your help.

      Best regards,

      nbv

      %E3%82%B9%E3%82%AF%E3%83%AA%E3%83%BC%E3%83%B3%E3%82%B7%E3%83%A7%E3%83%83%E3%83%88_2011-08-17_18.00.26.png

        • 1. Re: Multiple Relationships between Two Tables?
          philmodjunk

          Two options:

          A (Not the best option, but it works:)
          Define two fields in BrandANdSubBrand: BrandID, SubBrandID so that you get this relationship:

          BrandANdSubBrand::BrandID = Inventory::fkBrand AND
          BrandANdSubBrand::SubBrandID = INventory::fkSubBrand

          I only posted this to help show why your current relationship doesn't work. Please use the following option:

          B (A better approach)
          Add __pk_BandSID as an auto-entered serial number field. Add a Brand and SubBrand fields in BrandANDSubBrand. Remove them from Inventory. Replace them with _fk_BandSID. Remove your BrandID field.

          Define this relationship:

          BrandANdSubBrand::__pk_BandSID = Inventory::_fk_BandSID

          This pair of fields need not be displayed on any layout. They can be hidden from the users.

          On any inventory layout where you need to show the brand and sub brand, add the Brand and SubBrand fields from BrandANdSubBrand to your inventory layout and they'll display the correct information. You can even sort on these fields or perform finds where you enter criteria into them.