Mysql – How to structure a database to show data for each USA State and Canada Territory on a Per user basis

database-designMySQL

I need to build a PHP/MySQL application. I am a bit confused on how to do the database for a project like I am about to explain though.

The end result will be a Map of the USA and Canada. Each state will have a popup with data for that state. Below is the data items I need to show for each state.

Contacts (integer)
Pickups (integer)
Interviews (integer)
Guest Posts (integer)
Book Review Opp (integer)

Now to complex it a little more, each map page will show the data from above only for a particular User and then for a particular "Campaign" that the user owns.

Every user will have a map page with data only relevent to the campaign they are viewing.

I am not sure on how to structure a MySQL table for the "campaigns" and have it store data for the above items for each state or region of Canada.

Any ideas how to structure the database?

The image below is just an example, I need to be able to set these items

Contacts (integer)
Pickups (integer)
Interviews (integer)
Guest Posts (integer)
Book Review Opp (integer)

For every State, also each users campaign will mean a separate map where each of these items can be set again for each state

enter image description here

Best Answer

Well, I guess I will have to make assumptions about what your user and campaign data look like.

Maybe a schema like this:


Users
-----
  id (PK)

Campaigns
---------
  id (PK)
  user_id (This it the "user owns a campaign" relation, FK to users.ID)
  (other fields)

territories
-----------
  id (PK)
  name
  country

territories_campaigns
---------------------
  id (PK)
  territory_id (FK to territories.id)
  campaign_id (FK campaigns.id)

You use the territories_campaigns table to determine which campaign is happening in which territory. The structure of this table allows a campaign to span multiple territories, and each territory can host multiple campaigns.

Since the campaign table refers to the user who owns it, to build a map for a specific user, make sure you filter on the user_id

Related Question