Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

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

share|improve this answer
    
Thanks for taking the time to respond, I know I have not provided a lot of details, I really don't have a lot of details as i'm just trying to plan it out still. Based on your scheme above, would I be able to set these items Contacts (integer) Pickups (integer) Interviews (integer) Guest Posts (integer) Book Review Opp (integer) for each territory? I basically need to be able to set a value for each of these items, for each territory. For example the 50 states would have these values filled in for all 50 states on every campaign. Thanks for the help –  jasondavis Feb 13 at 23:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.