AnsweredAssumed Answered

Multiple Relationships between Two Tables?

Question asked by newbievin on Aug 17, 2011
Latest reply on Aug 17, 2011 by philmodjunk


Multiple Relationships between Two Tables?



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,