Answer to question on LinkedIn - decision tree

My answer was too long!! :) So posting the answer here with a link on linkedin

Question :

http://tiny.cc/russ_answer

What is the best way to do a "simple" IT expert file?

We have a project where we are trying to determine the best equipment for a particular type of wastewater project. We would like the user to specify the type of water, location, etc. and then we can provide an answer for the best type of equipment, regulations to follow, advice, etc.

Any suggestions on how to best do this?


My answer :


I would have 5 tables

3 main tables for the names and details eg:

equipment : bucket, small sewer, large sewer
location : house, factory
water_type : leak, sewerage

Then 2 tables pairing the options to the equipment

equipment_location : (Small Sewer, House), (Bucket, House), (Large Sewer, Factory), (Small Sewer, Factory), (Bucket, Factory)

equipment_water_type : (Small Sewer, Sewerage), (Large Sewer, Sewerage), (Small Sewer, Sewerage), (Bucket, Leak), (Bucket, Leak)

Then maybe create a view as equipment options eg:

equipment_name, location_name, water_type_name
Bucket, Factory, Leak
Bucket, House, Leak
Large Sewer, Factory, Sewerage
Small Sewer, Factory, Sewerage
Small Sewer, House, Sewerage

Then use a filter from the users form selections using whatever method, drop down lists, checkboxes etc.

eg: if the user selects house and leak

SELECT equipment_name
FROM equipment_options 
WHERE location_name='house' 
AND water_type_name='leak'

result = 'bucket'

eg: or if the user selects factory and sewerage

SELECT equipment_name
FROM equipment_options 
WHERE location_name='factory' 
AND water_type_name='sewerage'

results = 'small sewer' and 'large sewer'

I'm using names above, but a developer would use id's.

And here is a MySQL dump of something I prepared earlier ;) You can import this directly into MySQL




SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;




CREATE TABLE IF NOT EXISTS `equipment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;


INSERT INTO `equipment` (`id`, `name`) VALUES
(1, 'Large Sewer'),
(2, 'Small Sewer'),
(3, 'Bucket');


CREATE TABLE IF NOT EXISTS `equipment_location` (
  `equipment_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  PRIMARY KEY (`equipment_id`,`location_id`),
  KEY `location_id` (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `equipment_location` (`equipment_id`, `location_id`) VALUES
(2, 1),
(3, 1),
(1, 2),
(2, 2),
(3, 2);
CREATE TABLE IF NOT EXISTS `equipment_options` (
`equipment_id` int(11)
,`equipment_name` text
,`location_id` int(11)
,`location_name` text
,`water_type_id` int(11)
,`water_type_name` text
);
CREATE TABLE IF NOT EXISTS `equipment_water_type` (
  `equipment_id` int(11) NOT NULL,
  `water_type_id` int(11) NOT NULL,
  PRIMARY KEY (`equipment_id`,`water_type_id`),
  KEY `water_type_id` (`water_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `equipment_water_type` (`equipment_id`, `water_type_id`) VALUES
(1, 1),
(2, 1),
(3, 2);


CREATE TABLE IF NOT EXISTS `location` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;


INSERT INTO `location` (`id`, `name`) VALUES
(1, 'House'),
(2, 'Factory');


CREATE TABLE IF NOT EXISTS `water_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;


INSERT INTO `water_type` (`id`, `name`) VALUES
(1, 'Sewerage'),
(2, 'Leak');
DROP TABLE IF EXISTS `equipment_options`;


CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `equipment_options` AS select `e`.`id` AS `equipment_id`,`e`.`name` AS `equipment_name`,`l`.`id` AS `location_id`,`l`.`name` AS `location_name`,`w`.`id` AS `water_type_id`,`w`.`name` AS `water_type_name` from ((((`equipment` `e` join `equipment_location` `el` on((`el`.`equipment_id` = `e`.`id`))) join `equipment_water_type` `ew` on((`ew`.`equipment_id` = `e`.`id`))) join `location` `l` on((`l`.`id` = `el`.`location_id`))) join `water_type` `w` on((`w`.`id` = `ew`.`water_type_id`))) order by `e`.`name`,`l`.`name`,`w`.`name`;




ALTER TABLE `equipment_location`
  ADD CONSTRAINT `equipment_location_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`),
  ADD CONSTRAINT `equipment_location_ibfk_1` FOREIGN KEY (`equipment_id`) REFERENCES `equipment` (`id`);


ALTER TABLE `equipment_water_type`
  ADD CONSTRAINT `equipment_water_type_ibfk_4` FOREIGN KEY (`water_type_id`) REFERENCES `water_type` (`id`),
  ADD CONSTRAINT `equipment_water_type_ibfk_3` FOREIGN KEY (`equipment_id`) REFERENCES `equipment` (`id`);


/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;