I'm having trouble with something that ought to be quite simple. I want to categorise some products based on a major_id and a minor_id. These three items become dimensions in a pivot table. Sometimes the category is based on just looking at the major_id but for some of the categories I also need to consider the minor_id. Its like the table would look like:
ProductCategory:
LOAD * INLINE [
major_id, minor_id, "Product Group",
120, 30, "Shoes",
120, 51, "Trainers",
115, *, "Socks",
188, *, "Hats"
];
And the products like:
Products:
LOAD name, major_id, minor_id
SQL Select * FROM Products
Obviously this doesnt work with the wildcards in there but its the kind of thing I need. Where I specify both criteria it should use them, where I dont it should only use one. Ids are numeric not text so I cant use the wildcard matching in the cookbook.
The number of minor_ids under major_id 115 can be in the hundreds and can be added to quite often so I dont want my inline ProductCategory table to be 'hard coded' with every combination for a category.
The criteria for a category is also going to be extended to other attributes too which would make the table even larger. Its almost like my mapping table is really a 'rules' table. and this is where I am stumped.
