Search code examples
phpmysqlcountries

Show contact person per country & city

I'm working on a website with different contact persons in different countries & cities.

What I would like to achieve is when a visitor visits a page with info about France - Paris, the script will search for the contact person in the database and show the info on the page.

Problem is that a contact person can have multiple countries/cities under his responsibility.

I already have a countries and cities table. I don't want to change or add anything to these tables.

So the only thing I can think of is:

contact_persons (table)

contact_persons_id

contact_persons_name

And other info...

contact (table)

contact_persons_id

country

city

So for example:

moonwalker - France - Paris

moonwalker - France - Marseille

otheruser - Netherlands - Utrecht

But is this ok? Or is there some other way to solve this?

Thanks in advance.

p.s.: Languages are PHP and MySql.

Solution

  • You can normalize your data in a way like described below. You have Contacts, Cities, and Countries as separate areas of data to avoid repetition. Then you have a table to map contacts to cities in a one-to-many relationship (one contact = many cities).

    [table_contacts]
  - id
  - name

[table_cities]
  - id
  - name
  - country_id

[table_countries]
  - id
  - name

[table_contacts_cities]
  - id
  - contact_id
  - city_id

    [EDIT] Simplified thanks to comment below.