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.
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.
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".
Thanks for your help! I will try this out.