List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 22 2005 6:35pm
Subject:Re: MySQL constraint question
View as plain text  
Gerald Taylor <platypus@stripped> 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








Thread
MySQL 4.1.9 - Requesting Optimization TipsMatt Florido18 Feb
  • Re: MySQL 4.1.9 - Requesting Optimization TipsPaul DuBois18 Feb
    • Re: MySQL 4.1.9 - Requesting Optimization TipsMatt Florido18 Feb
      • Re: MySQL 4.1.9 - Requesting Optimization TipsPaul DuBois18 Feb
      • MySQL constraint questionGerald Taylor18 Feb
        • Re: MySQL constraint questionSGreen22 Feb
          • Re: MySQL constraint questionMartijn Tonies22 Feb
            • Re: MySQL constraint questionGerald Taylor23 Feb