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