We have an optimization question that probably could be easily resolved with some of the additional features of some commercial database products, but, being stubborn about migrating away from MySQL (What can't it do again?) I'm looking for a solution to a particular design and optimization issue we are having. This may of course not be the most appropriate list for the question, but, it does contains perhaps the best group of individuals for which to pose it to. 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.. but, perhaps there exists some additional table hierarchies that I'm not thinking about to easily compare multiple levels of data. 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/ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
