lundi 23 février 2015

Database design and normalization

I am trying to create a database for tracking files. We have an input file. Then a person can buffer a Shapefile by a distance. This would result in an output. The output files could be grouped into different categories like mammal and then all files with a group of mammal will be union together creating the composite file. I am not sure if I have the group buffer join table correct. Is this correct? I tired to add a schema, but I don't have a high enough reputation.


CREATE TABLE IF NOT EXISTS grit.buffer_type_lu ( id INT NOT NULL AUTO_INCREMENT, buffer_type TEXT NOT NULL, PRIMARY KEY (id)) ENGINE = InnoDB COMMENT = 'This is to store if the buffer file is a postive or negitive buffer distance.';


CREATE TABLE IF NOT EXISTS grit.unit_conversion ( id INT NOT NULL AUTO_INCREMENT, unit_name TEXT NOT NULL, conversion INT NOT NULL, atl_name TEXT NOT NULL, drop_down_order INT NOT NULL, PRIMARY KEY (id)) ENGINE = InnoDB COMMENT = 'This is to store the unit conversion information. All distances are converted to millimeters. This is to essentially convert the units to base 10 in millimeters. \n\nhttps://www.daniweb.com/software-development/python/threads/411612/question-about-basic-unit-converter-project#post2131640\n\nhttp://www-inst.eecs.berkeley.edu/~selfpace/cs9honline/P2a/\n\n\n\n';


CREATE TABLE IF NOT EXISTS grit.buffer_distance ( id INT NOT NULL AUTO_INCREMENT, buffer_distance INT NOT NULL, unit_conversion_id INT NOT NULL, PRIMARY KEY (id), INDEX fk_buffer_distance_unit_conversion1_idx (unit_conversion_id ASC), CONSTRAINT fk_buffer_distance_unit_conversion1 FOREIGN KEY (unit_conversion_id) REFERENCES grit.unit_conversion (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB COMMENT = 'This is to store the buffer distance for the issue dataset. \n\nThe other powerful thing about this table is that it could also support buffering the roads based on effective distance.


CREATE TABLE IF NOT EXISTS grit.buffer_op ( id INT NOT NULL AUTO_INCREMENT, buffer_name TEXT NOT NULL, buffer_distance_id INT NOT NULL, issues_id INT NOT NULL, issues_input_id INT NOT NULL, buffer_type_lu_id INT NOT NULL, geometry_type_lu_id INT NOT NULL, PRIMARY KEY (id, buffer_distance_id, issues_id, issues_input_id), INDEX fk_buffer_op_issues1_idx (issues_id ASC, issues_input_id ASC), INDEX fk_buffer_op_buffer_type_lu1_idx (buffer_type_lu_id ASC), INDEX fk_buffer_op_geometry_type_lu1_idx (geometry_type_lu_id ASC), INDEX fk_buffer_op_buffer_distance1_idx (buffer_distance_id ASC), CONSTRAINT fk_buffer_op_issues1 FOREIGN KEY (issues_id) REFERENCES grit.input (id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_buffer_op_buffer_type_lu1 FOREIGN KEY (buffer_type_lu_id) REFERENCES grit.buffer_type_lu (id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_buffer_op_geometry_type_lu1 FOREIGN KEY (geometry_type_lu_id) REFERENCES grit.geometry_type_lu (id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_buffer_op_buffer_distance1 FOREIGN KEY (buffer_distance_id) REFERENCES grit.buffer_distance (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB COMMENT = 'This is to store the name of the output buffer file. ';


CREATE TABLE IF NOT EXISTS grit.group_lu ( id INT NOT NULL AUTO_INCREMENT, group_name TEXT NOT NULL, group_definition TEXT NOT NULL, PRIMARY KEY (id)) ENGINE = InnoDB COMMENT = 'This is to store the groups that will use for the union. The group look up table stores the groups used to union all the issue datasets together to create an output file. ';


CREATE TABLE IF NOT EXISTS grit.group_buffer_join ( id INT NOT NULL AUTO_INCREMENT, group_lu_id INT NOT NULL, buffer_op_id INT NOT NULL, buffer_op_buffer_distance_id INT NOT NULL, buffer_op_issues_id INT NOT NULL, buffer_op_issues_input_id INT NOT NULL, PRIMARY KEY (id, buffer_op_id, buffer_op_buffer_distance_id, buffer_op_issues_id, buffer_op_issues_input_id), INDEX fk_group_buffer_join_buffer_op1_idx (buffer_op_id ASC, buffer_op_buffer_distance_id ASC, buffer_op_issues_id ASC, buffer_op_issues_input_id ASC), INDEX fk_group_buffer_join_group_lu1_idx (group_lu_id ASC), CONSTRAINT fk_group_buffer_join_buffer_op1 FOREIGN KEY (buffer_op_id , buffer_op_buffer_distance_id , buffer_op_issues_id , buffer_op_issues_input_id) REFERENCES grit.buffer_op (id , buffer_distance_id , issues_id , issues_input_id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_group_buffer_join_group_lu1 FOREIGN KEY (group_lu_id) REFERENCES grit.group_lu (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB COMMENT = 'This is to store the join between group lookup and the buffer file. This is needed because there is a 1 to many relationship between buffer and group. ';


CREATE TABLE IF NOT EXISTS grit.composite_issue_op ( id INT NOT NULL AUTO_INCREMENT, composite_name TEXT NOT NULL, group_buffer_join_id INT NOT NULL, group_buffer_join_buffer_op_id INT NOT NULL, group_buffer_join_buffer_op_buffer_distance_id INT NOT NULL, group_buffer_join_buffer_op_issues_id INT NOT NULL, group_buffer_join_buffer_op_issues_input_id INT NOT NULL, PRIMARY KEY (id, group_buffer_join_id, group_buffer_join_buffer_op_id, group_buffer_join_buffer_op_buffer_distance_id, group_buffer_join_buffer_op_issues_id, group_buffer_join_buffer_op_issues_input_id), INDEX fk_composite_issue_op_group_buffer_join1_idx (group_buffer_join_id ASC, group_buffer_join_buffer_op_id ASC, group_buffer_join_buffer_op_buffer_distance_id ASC, group_buffer_join_buffer_op_issues_id ASC, group_buffer_join_buffer_op_issues_input_id ASC), CONSTRAINT fk_composite_issue_op_group_buffer_join1 FOREIGN KEY (group_buffer_join_id , group_buffer_join_buffer_op_id , group_buffer_join_buffer_op_buffer_distance_id , group_buffer_join_buffer_op_issues_id , group_buffer_join_buffer_op_issues_input_id) REFERENCES grit.group_buffer_join (id , buffer_op_id , buffer_op_buffer_distance_id , buffer_op_issues_id , buffer_op_issues_input_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB COMMENT = 'This is to store the name of the output composition file. ';


Thank you, Luke


Aucun commentaire:

Enregistrer un commentaire