Flow (Recipe) Data Suggestions?

I'm looking for a solution to a particular design and optimization issue we are having. We are using MySQL to help everyone understand the limits of our capabilities within its SQL implementation.

Here's the basic overview:
We are (re)designing a manufacturing process flow (recipe) system which tracks the various processess/specficiations/parameters that a widget sees from raw material to assembly.

So, essentially, we have Process Steps (generalized steps in the manufacturing process), which contain specifications (like do it for x seconds, with y tool) and parameters (do it at 80 degrees C, do it at 200T pressure). So, a flow (recipe) would contain process steps which the associated parameters/specifications in a given sequence.

To accomodate this we have the following table structures:

---



#The basic flow information


CREATE TABLE base_flows (
  flowID int(10) unsigned NOT NULL default '0',
  flow_title char(100) NOT NULL default '',
  date_added int(10) unsigned NOT NULL default '0',
  added_by mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (flowID)
);



#Process steps


CREATE TABLE process_steps (
  process_stepID mediumint(8) unsigned NOT NULL default '0',
  process_step_name char(50) NOT NULL default '',
  process_step_type tinyint(3) unsigned NOT NULL default '0',
  process_step_location tinyint(3) unsigned NOT NULL default '0',
  toolID mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (process_stepID)
);



# Names of step specifications/parameters (since names 


# like Pressure) might be reused with varying values


CREATE TABLE step_specification_names (
  step_spec_nameID mediumint(8) unsigned NOT NULL default '0',
  step_spec_name char(50) NOT NULL default '',
  step_spec_type tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (step_spec_nameID),
  KEY step_spec_type (step_spec_type)
);



# Step specifications (containing the target value, a unitID 

# referencing measurement units (T, sec, C), max and min ranges

# that we want to hit, and step_spec_nameID to reference the


# step_spec_name (Pressure, Temp)


CREATE TABLE step_specifications (
  step_specID mediumint(8) unsigned NOT NULL default '0',
  step_spec_nameID mediumint(8) unsigned NOT NULL default '0',
  target_value float NOT NULL default '0',
  unitID mediumint(8) unsigned NOT NULL default '0',
  target_max float NOT NULL default '0',
  target_min float NOT NULL default '0',
  date_added int(10) unsigned NOT NULL default '0',
  added_by mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (step_specID),
  KEY step_spec_nameID (step_spec_nameID),
  KEY target_value (target_value)
);



#The actual recipe collections (sequences)


CREATE TABLE base_flow_recipes (
  flowID int(10) unsigned NOT NULL default '0',
  process_stepID mediumint(8) unsigned NOT NULL default '0',
  step_specID mediumint(8) unsigned NOT NULL default '0',
  seq tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (flowID,process_stepID,step_specID,seq),
  KEY process_stepID (process_stepID),
  KEY seq (seq),
  KEY step_specID (step_specID)
);

----

This works reasonably well (We can fairly easily query the above data
to build recipes).  But, enter in some snags.

1) In order to avoid creating thousands of recipes we do things called
"SPLITS" when we are in a development phase.  Meaning, we take a few
parameters and change the values / order (meaning, if we were making
bread we might use 2 cups of flour as compared to 3, and do so after
adding the water as opposed to before).  So, we need a smart way to
store changes to the above flows without recreating whole new flows.

2) It turns out not all values are clear cut floats.  Ie, we might
have odd ball parameters that really only have ALPHA representations. 
Ie, instead of it always being something like 10.2 F we might have to
store LLL Oven).

So, the obvious solution might be to remove the floats and use chars
in the step_specifications table.  Well, wait until you see 3.

3) We need a smart way to SCORE SIMILARITY between recipes (and, to
make it even more complex, score similarity taking into account
splits).  Meaning, we want to be able to say "give me the flows the
closest resembly THIS flow".   Or, more accurately, show me the
widgets that closest resemble this widget.  Meaning it should be smart
enough to  know that 100 C is close to 90 C then is 110 C in process
step 5 of two compared flows.  This is why chars become difficult to
deal with.  Sounds like the job of a recursive select, which isn't
available in MySQL, but, perhaps there exists some additional table
hierarchies that I'm not thinking about to easily compare multiple
levels of data.  Essentially, going back to the bread example we  want
to find a way to say that Rye bread is closer to Wheat bread then it
is white bread based on the order of process steps and their
specifications/parameters.  Obviously, then this would have to be
smart enough to cross reference some table of measurement units and
their values (ie, C to F, T or mT etc.).

Clearly not all of this can be done in SQL (or, I'd be amazed if it
could).. but, I am looking for the most sane table and query structure
to build off of to generate this data.. I just don't get the sense
that our approach will scale, and, support any level of scoring logic
such as in #3.

Your brilliance is requested and thanked in advance,
Seth

---
Seth Northrop
Manager of Information Technology
Reflectivity, Inc.
3910 Freedom Circle, Suite 103
Santa Clara, CA 95054
voice:  408-970-8881 x147
fax:    408-970-8840
http://www.reflectivity.com/