I have a requirement i got from my father. He gave me a table and wanted me to digitalis it. So that he can the system can do all the dirty jobs for him ( that's why there are developers in the world, isn't it). The table like this (i created it in HTML + jQuery)
and i managed to store it to mySQL data base and the it looks like this.
Crop: the final table
Char: altitiude, mean_temperature …. are all characteristics
s: s1,s2,s3,n
range: for each data there may be multiple ranges
but i am notconvinced this is the right way to do it! I know this because i can't write a query to retrieve the data with a convincing query.
My Question
How should i structure the database so that
- The data can be stored in time and storage optimized manner
- The data can be retrieved in timely manner using as short as possible query.
EDITED
mySQL DDL
--
-- Database: `landuse`
--
CREATE DATABASE IF NOT EXISTS `landuse` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `landuse`;
-- --------------------------------------------------------
--
-- Table structure for table `char`
--
CREATE TABLE IF NOT EXISTS `char` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`s1` int(11) NOT NULL,
`s2` int(11) NOT NULL,
`s3` int(11) NOT NULL,
`n` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=271 ;
--
-- RELATIONS FOR TABLE `char`:
-- `n`
-- `s` -> `id`
-- `s1`
-- `s` -> `id`
-- `s2`
-- `s` -> `id`
-- `s3`
-- `s` -> `id`
--
-- --------------------------------------------------------
--
-- Table structure for table `crop`
--
CREATE TABLE IF NOT EXISTS `crop` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`altitude` int(11) NOT NULL,
`mean_temperature` int(11) NOT NULL,
`LGP` int(11) NOT NULL,
`rainfall_during_LGP` int(11) NOT NULL,
`soil_drainage` int(11) NOT NULL,
`effective_soil_depth` int(11) NOT NULL,
`stones_rock_outcrops` int(11) NOT NULL,
`slope_angle` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;
--
-- RELATIONS FOR TABLE `crop`:
-- `LGP`
-- `char` -> `id`
-- `altitude`
-- `char` -> `id`
-- `effective_soil_depth`
-- `char` -> `id`
-- `mean_temperature`
-- `char` -> `id`
-- `rainfall_during_LGP`
-- `char` -> `id`
-- `slope_angle`
-- `char` -> `id`
-- `soil_drainage`
-- `char` -> `id`
-- `stones_rock_outcrops`
-- `char` -> `id`
--
-- --------------------------------------------------------
--
-- Table structure for table `land`
--
CREATE TABLE IF NOT EXISTS `land` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`altitude` float NOT NULL,
`mean_temperature` float NOT NULL,
`LGP` float NOT NULL,
`rainfall_during_LGP` float NOT NULL,
`soil_drainage` varchar(10) NOT NULL,
`effective_soil_depth` float NOT NULL,
`stones_rock_outcrops` float NOT NULL,
`slope_angle` float NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
-- --------------------------------------------------------
--
-- Table structure for table `range`
--
CREATE TABLE IF NOT EXISTS `range` (
`id` int(11) NOT NULL,
`min` float NOT NULL,
`max` float NOT NULL,
`const` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- RELATIONS FOR TABLE `range`:
-- `id`
-- `s` -> `id`
--
-- --------------------------------------------------------
--
-- Table structure for table `s`
--
CREATE TABLE IF NOT EXISTS `s` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1170 ;
The following is the php code that inserts the data from the view! (the view sends an json data)
PHP
$crop = $_REQUEST["crop"];
$crop_data = json_decode($crop);
try {
$dbh = new PDO('mysql:host=localhost;dbname=landuse', 'root', '', array(PDO::ATTR_PERSISTENT => true));
$dbh->beginTransaction();
// foreach($dbh->query("SELECT * FROM s") as $row){
// print_r($row);
// }
$insert_range = $dbh->prepare("INSERT INTO `range`(id, `min`, `max`)VALUES (:s_id,:min,:max);");
$insert_range_drainage = $dbh->prepare("INSERT INTO `range`(id, const)values(:s_id,:const);");
$insert_s = $dbh->prepare("INSERT INTO s(id, type)values(NULL,:type);");
$insert_character = $dbh->prepare("INSERT INTO `char` (`id`, `s1`, `s2`, `s3`, `n`) VALUES (NULL, :s1, :s2, :s3, :n);");
$insert_crop = $dbh->prepare(
"INSERT INTO `crop` (`id`, `name`, `altitude`, `mean_temperature`, `LGP`, `rainfall_during_LGP`, `soil_drainage`, `effective_soil_depth`, `stones_rock_outcrops`, `slope_angle`)
VALUES (NULL,:name,:altitude,:mean_temperature,:LGP,:rainfall_during_LGP, :soil_drainage, :effective_soil_depth, :stones_rock_outcrops, :slope_angle)");
$altitude=0;
$mean_temperature=0;
$LGP=0;
$rainfall_during_LGP=0;
$soil_drainage=0;
$effective_soil_depth=0;
$stones_rock_outcrops=0;
$slope_angle=0;
foreach ($crop_data as $key => $c) {
if($key=="name"){
break;
}
//for each characteristics
//$key is the name of the current characteristics
//$c is the array of the values (s1,s2,s3,n)
//the following are to hold the id's of s that are stored
$s1 = 0;
$s2 = 0;
$s3 = 0;
$n = 0;
foreach ($c as $s => $val) {
//for each s
//save it to the data base
$insert_s->bindParam(":type", $s);
if (!$insert_s->execute()) {
$dbh->rollBack();
echo '<div style="background-color: red">s not inserted</div>';
};
//get the id of the saved 's'
$s_id = 0;
foreach ($dbh->query("SELECT LAST_INSERT_ID() as last_id;") as $row) {
$s_id = $row["last_id"];
}
//set the respective field for each s
if($s=="s1"){
$s1=$s_id;
echo "S1=".$s_id;
}elseif($s=="s2"){
$s2=$s_id;
echo "S2=".$s_id;
}elseif($s=="s3"){
$s3=$s_id;
echo "S3=".$s_id;
}elseif($s=="n"){
$n=$s_id;
echo "N=".$s_id;
}
foreach ($val as $range) {
//for each range
//save the range in the data base.
if ($key == "soil_drainage") {
$insert_range_drainage->bindParam(":s_id", $s_id);
$insert_range_drainage->bindParam(":const", $range->min);
if (!$insert_range_drainage->execute()){
$dbh->rollBack();
echo '<div style="background-color: red">range not inserted (drainage)</div>';
};
}else{
$insert_range->bindParam(":s_id", $s_id);
$insert_range->bindParam(":min", $range->min);
$insert_range->bindParam(":max", $range->max);
if (!$insert_range->execute()) {
$dbh->rollBack();
echo '<div style="background-color: red">range not inserted</div>';
};
}
}
}
//insert characteristics to the database
$insert_character->bindParam(":s1",$s1);
$insert_character->bindParam(":s2",$s2);
$insert_character->bindParam(":s3",$s3);
$insert_character->bindParam(":n",$n);
if (!$insert_character->execute()) {
$dbh->rollBack();
echo '<div style="background-color: red">character not inserted</div>';
};
//Get the id of the current character saved
$character_id = 0;
foreach ($dbh->query("SELECT LAST_INSERT_ID() as last_id;") as $row) {
$character_id = $row["last_id"];
}
if($key=="altitude"){
$altitude=$character_id;
}elseif($key=="mean_temperature"){
$mean_temperature=$character_id;
}elseif($key=="LGP"){
$LGP=$character_id;
}elseif($key=="rainfall_during_LGP"){
$rainfall_during_LGP=$character_id;
}elseif($key=="soil_drainage"){
$soil_drainage=$character_id;
}elseif($key=="effective_soil_depth"){
$effective_soil_depth=$character_id;
}elseif($key=="stones_rock_outcrops"){
$stones_rock_outcrops=$character_id;
}elseif($key=="slope_angle"){
$slope_angle=$character_id;
}
}
//Insert the crop
$insert_crop->bindParam(":name",$crop_data->name);
$insert_crop->bindParam(":altitude",$altitude);
$insert_crop->bindParam(":mean_temperature",$mean_temperature);
$insert_crop->bindParam(":LGP",$LGP);
$insert_crop->bindParam(":rainfall_during_LGP",$rainfall_during_LGP);
$insert_crop->bindParam(":soil_drainage",$soil_drainage);
$insert_crop->bindParam(":effective_soil_depth",$effective_soil_depth);
$insert_crop->bindParam(":stones_rock_outcrops",$stones_rock_outcrops);
$insert_crop->bindParam(":slope_angle",$slope_angle);
echo 'dfghjkl;'.$crop_data->name;
if (!$insert_crop->execute()) {
$dbh->rollBack();
print_r($insert_crop->errorInfo());
echo '<div style="background-color: red">crop not inserted</div>';
};
$dbh->commit();
} catch (PDOException $x) {
echo '<br/>' . $x->getMessage();
}
here is a sample json data
{
"altitude":{
"s1":[
{
"min":"100",
"max":"100"
}
],
"s2":[
{
"min":"100",
"max":"100"
}
],
"s3":[
{
"min":"100",
"max":"100"
}
],
"n":[
{
"min":"100",
"max":"100"
}
]
},
"mean_temperature":{
"s1":[
{
"min":"100",
"max":"100"
}
],
"s2":[
{
"min":"100",
"max":"100"
},
{
"min":"100",
"max":"100"
},
{
"min":"0.01",
"max":"0.01"
}
],
"s3":[
{
"min":"100",
"max":"100"
}
],
"n":[
{
"min":"100",
"max":"100"
}
]
},
"LGP":{
"s1":[
{
"min":"100",
"max":"100"
}
],
"s2":[
{
"min":"100",
"max":"100"
}
],
"s3":[
{
"min":"100",
"max":"100"
},
{
"min":"200",
"max":"300"
}
],
"n":[
{
"min":"100",
"max":"100"
}
]
},
"rainfall_during_LGP":{
"s1":[
{
"min":"100",
"max":"100"
}
],
"s2":[
{
"min":"100",
"max":"100"
},
{
"min":"100",
"max":"100"
}
],
"s3":[
{
"min":"100",
"max":"100"
}
],
"n":[
{
"min":"100",
"max":"100"
}
]
},
"soil_drainage":{
"s1":[
{
"min":"MW"
}
],
"s2":[
{
"min":"W"
}
],
"s3":[
{
"min":"MW-W"
},
{
"min":"MW-I"
}
],
"n":[
{
"min":"I"
}
]
},
"effective_soil_depth":{
"s1":[
{
"min":"100",
"max":"100"
}
],
"s2":[
{
"min":"100",
"max":"100"
}
],
"s3":[
{
"min":"100",
"max":"100"
}
],
"n":[
{
"min":"100",
"max":"100"
}
]
},
"stones_rock_outcrops":{
"s1":[
{
"min":"100",
"max":"100"
}
],
"s2":[
{
"min":"100",
"max":"100"
}
],
"s3":[
{
"min":"100",
"max":"100"
},
{
"min":"100",
"max":"100"
}
],
"n":[
{
"min":"100",
"max":"100"
}
]
},
"slope_angle":{
"s1":[
{
"min":"100",
"max":"100"
}
],
"s2":[
{
"min":"100",
"max":"100"
}
],
"s3":[
{
"min":"100",
"max":"100"
},
{
"min":"100",
"max":"100"
}
],
"n":[
{
"min":"100",
"max":"100"
}
]
},
"name":"Maize"
}
Best Answer
A problem could be you are trying to store the measurements in a single table that you are 'navigating' to via a suitability table. It makes sense but you may want to consider separate tables
from your form, it looks like a measurement may have multiple ranges for the same suitability? you may need to add a sequence like attribute or further normalise if this is the case.