4 Replies Latest reply on Apr 17, 2009 1:02 PM by cmdunn1

    Help With Data Structure



      Help With Data Structure


      I am fairly new to FM and need a little help setting up a data structure. I need to have put products into multiple categories. The place where I am stumped is the categories have to be broken down like this:

      Product Line : Category : Subcategory

      Products will then be assigned to the subcategory but the problem is each product could be in different subcategories in another product line. For example:

      Brand A : Category 1 : Subcategory 1 : Product 1
      Brand B : Category 1 : Subcategory 1 : Product 1


      Brand A : Category 1 : Subcategory 1 : Product 1
      Brand A : Category 4 : Subcategory 3 : Product 1

      Any suggestions will be greatly appreciated!

        • 1. Re: Help With Data Structure

          Basically when you have many things to say about one thing (Product), it calls for another table, for these multiple "product attribute sets."* It would have (at least) 4 fields:






          It would be related to the Product table via Product_ID (which would be a unique auto-entered serial or UID ID in the Product table, just a plain field in the child/join table). Then you could have any combo of your attributes, with a record in the child table for every unique combo. These child records would be created using a portal in Products, with [x] Allow creation of related records turned on (likely [x] Delete related records also, on the CHILD side).


          I say "child/join" because I think of the child table more as a "self-join". In many cases you would be looking "through" it back to the Products table itself. To do such things as show "all products of this category", or subcategory, etc.. 


          I wonder about Brand. It seems to me that in most situations a "product" from a different Brand, which is in other ways similar is actually a different Product. It usually is to the consumer. But looked at in a more general way they are the same. A little confusing. I would tend to think, 2 Products, sharing the same attribute sets. 


          *"Attribute Sets" is a term from XML, but it seems to best describe this. 

          • 2. Re: Help With Data Structure

            I was thinking about creating four tables.


            Brands -<--Categories -<--Subcategories -->- Products 


            Does this seem logical? I tend to think there would be a many to many relationship that exists between Brand and Categories because one Brand could have many Categories and a Category could exist for many Brands.

            • 3. Re: Help With Data Structure

              I would not do that. Yes, each one could have its own table. But these are what I call "reference" tables. They would contain all the possible entries; possibly even with an auto-enter serial ID. Their main use would be for Value Lists, in order to control what values were entered (you do not want misspellings).


              But they are not the data entry "child/join" table that I was talking about. It would have the fields I mentioned. Whether a particular field in the join table is empty is not a problem (except Product_ID, which cannot be empty).


              You would access data in the join table via either Finds or relationships. For example, a relationship from a particular category value, from any other table linked to the Product_Attributes (there, I've given it a name :-), could show all products of that same category.* There are several uses for this. 


              *Actually it would look "through" the join table to another table occurrence of Product. Because the join table contains only the Product ID, the name is always only in the Products table, at least in proper relational design. Makes it easy to change the name at any time, with no maintenance required. That is why I think of it as a "join". 

              • 4. Re: Help With Data Structure
                   Thanks for your help! I will try this out.