be OK with it without that nagging feeling that somebody will say upa
if you just put xxx in the database then you could filter all that in your query. If it ever happens I can say, ah yes but when I wrote that
we were only on version 4.
When you have a problem set that pushes the envelope of what it
can do... that's how people get the ideas to make it do a new level of things in the future, such as you are envisioning.
Martijn Tonies wrote:
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]
