I have a DB with 200+ categories and 3500+ products. There are two columns in the product category 'model' and 'top_model'. The 'model' is the SKU (top_model with attributes). An example would be a tee shirt 'top-model' AB101; 'model' AB101-sm-blue. So if it came in 4 different sizes and 4 different colors there would be 16 'models' with 1 'top_model'. I am wondering if I should build a look-up table with two columns 'top_model' and 'model_limit' to create a limit boundary.
This would only need to be accessed where 'model' <> 'top_model'.
If 'model' = 'top_model' limit '1' else limit = model_limit from qty_table where 'top_model' = 'AB101'.
Yes, I am aware the syntax isn't accurate, just trying for brevity.
This seems to be counter-productive...without a bit more detail/explanation (or just better wording, maybe) I cannot figure out what exactly you want.
It seems it would be quite easy to parse the products table to build this fairly quickly.
This sentence is confusing.
If you want to setup a constraint that a given top-model can have no more than N models you would have to do two things:
1) Create what sounds like your "limit table" to hold the constraints
2) "CREATE TRIGGER" on product to perform the query of product for present count and compare it to the current value on the limit table.
You would probably also want a trigger on the limit table to ensure records cannot be added or modified in such a way that the products table becomes invalid.