> > 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.
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...
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL