FusionMaps with ASP > Plotting Data from Database > Database Structure

Before going deeper into the codes and their logics, let us look at the database structure first. The database has been named as FusionMapsDB.
 
The Access database is present in Download Package > Code > ASP > DB_Drill > DB.
 
Tables
We have used 4 tables in the database, here are the details of all the tables.
 
Table name: fcmap_master
 
This table stores the intenal IDs of each state in  US map and their corresponding map file names.
Field Type Description
Internal_Id Text This represents individual State present in the US MAP.
s_name Text This stores short name of the states used in US MAP.
l_name Text This stores full name of the states in US MAP.
map_swf Text This is FusionMap Flash File name.
 
We use different .swf files to display different maps. This table stores the .swf file name required to display the maps in our example along with the Internal Ids of that map. We also store abbreviated and real names of the parts of the maps in this table.
 
 
Table name: fcmap_group_master
 
This table stores the names of major Category groups regarding employment.
Field Type Description
group_id Number Group ID i.e 1/2/3
group_name Text Group Name i.e business, service, unemployed
 
This table stores the name and id of the employment groups, i.e. Business and Service. It also stores 'Unemployed' as a group here.
 
Table name: fcmap_subgroup_master
 
This table stores the subgorup names of each group said above.
Field Type Description

group_id

Number

Group id of Group Master

subgroup_id Number Subgroup id i.e 1/2/3/4/5
subgroup_name Text Subgroup name of individual Group Master i.e business -> software , business -> automobile
 
Each group is divided into various sub-groups, e.g.
  • We divide Business into Automobile, Communication, Evergy, Finance and Software subgroups.
  • We divide Service under Administration, Education, Law, Postal and Security subgroups.
  • We divide Unemplyed group into different age-groups like 20-30 years, 30-40 years, 40-50 years, 50-60 years and 60 above.
This table stores subgroup names, unique subgroup ids and the parent group id for each subgroup.
 
Table name: fcmap_distribution
 
This table stores the data for each subgroup for each entity for each state. Hence, this table is the main storehouse of data which we will use in our code.
Field Type Description
Internal_Id Text This represents individual State present in the USA MAP [Internal Id from fcmap_master]
entity_id Text This represents individual internal id of eatch USA State entity
group_id Number group id related to fcmap_group_master
subgroup_id Number subgroup id related to fcmap_subgroup_master
data Number This field stores the actual population data for each sub group of each county of each state of US.
 
fcmap_distribution stores the actual data that are to be plotted in our example. It stores the number of people in each of the subgroups mentioned above along with respective subgroup id, group id, county code and state code.
 
Now, let's look at the Relations among these tables.
 
Relations
The snap below shows the relationship among all the tables used in this database.