Re: Creating a limits table

On Tue, Apr 5, 2016 at 5:14 AM, Kurt R Marquardt <marqkdsade(at)gmail(dot)com>
wrote:

> 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.
>
>
​What's a "limit boundary"?​

qty_table
>
> top_model | model_limit
> -----------------|------------------
> AB101 | 16
>
> 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.

David J.