> > 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.
Disagreed :-) A decent database system would be able to create multi-row check constraints -> there are several types of constraints. 1) column constraints 2) table constraints 3) database/schema constraints These would fall under (3) I guess. Firebird allows queries in its check constraints, but only enforces the constraints at INSERT or UPDATE time. A database system that would support "deferred" constraints should be able to create multi-table, multi-row check constraints just fine. However, I don't know any DBMS that currently does that. Perhaps Mimer or ThinkSQL... With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
