Re: MySQL constraint question
Thanks for these answers. I can now write this in the application and 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_idpoints to category min_percent max_percent and the individual ingrediants ing_id name price category_idamong 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]
Re: MySQL constraint question
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_idpoints to category min_percent max_percent and the individual ingrediants ing_id name price category_idamong 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
Re: MySQL constraint question
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_idpoints to category min_percent max_percent and the individual ingrediants ing_id name price category_idamong 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]
MySQL constraint question
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_idpoints to category min_percent max_percent and the individual ingrediants ing_id name price category_idamong 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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
