FusionMaps with PHP > 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 MySQL dump of the database is present in Download Package > Code > PHP > DB_Drill > DB.
 
Tables
We have used 4 tables in the database, here are the details of all the tables.
Table name: fcmp_master
 
This table stores the intenal IDs of each state in  US map and their corresponding map file names.
Field Type Description

Internal_Id

varchar(10)

This represents individual State present in the US MAP.

s_name

varchar(10)

This stores short name of the states in US MAP.

l_name

varchar(50)

This stores full name of the states in US MAP.

map_swf

varchar(50)

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 int(10) Group ID i.e 1,2,3

group_name

varchar(50)

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 for each of the groups said above.
Field Type Description

group_id

int(10)

Group id of Group Master

subgroup_id int(10) Subgroup id i.e 1,2,3,4

subgroup_name

varchar(50)

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 into 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

varchar(10)

This represents individual State present in the USA MAP [Internal Id from fcmap_master]

entity_id

varchar(10)

This represents individual internal id of eatch USA State entity.

group_id

int(10)

group id related to fcmap_group_master

subgroup_id

int(10)

subgroup id related to fcmap_subgroup_master

data

numeric(10)

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
 
fcmap_group_master.group_id = fcmap_subgroup_master.subgroup_id [Link with fcmap_group_master and fcmap_subgroup_master Table]
fcmap_distribution.group_id = fcmap_group_master.group_id [Link with fcmap_distribution and fcmap_group_master Table]
fcmap_distribution.subgroup_id = fcmap_subgroup_master.subgroup_id [Link with fcmap_distribution and fcmap_subgroup_master Table]
fcmap_master.Internal_Id = fcmap_distribution.Internal_Id [Link with fcmap_master and fcmap_distribution Table]