Gerald Taylor <[EMAIL PROTECTED]> wrote on 02/18/2005 10:18:29 AM: > I have a database thats full of ingredients that are placed in various > categories. and then there are mixtures that are allowed to > be labelled with a certain grade based on the quality and > composition of the ingredients it is comprised from. > But the formulas are not always the same, as long as the profile matches
> certain criterian. > > For example, in order to be label grade Premium, > it must have between 70% and 95% ingredients from group A > between 0 and 15 % from group B > between 5 and 15% from group C > between 0 and 15% from group D > > > What you actually put in the mix is determined by price > and availability and other factors. > > So I implement these profiles with a mySQL table > I have > > grade_id points to main grade record > category_id points to category > min_percent > max_percent > > and the individual ingrediants > > ing_id > name > price > category_id among others... > > The question is > Is there some way mysql can assure the integrity of these profiles, so > that there is no way to use a set of records for a grade that can't add > upto 100%. Or is this pretty much application logic? > > > Nope, the "combined total < 100%" calculation will have to be application-based as it requires validating an inserted or updated row based on the value(s) contained in other row(s). CHECK constraints, which are in the development pipeline, can only be used to validate a row against constant values or some combination of values from within the row being evaluated. Any time you need to compare a group of rows in order to validate the group, you have left the automation of SQL logic and are in the realm of application logic, as you guessed. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
