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.