Help with schema design for address info

nickweavers · #1 (**permalink**) 01-04-05, 18:52

I would like to record the locations of registered members of my site and match them to people with similar interests in nearby locations. I devised the following tables to hold the names of countries, regions and towns and a unique ID for each, along with a table called locations which has a columns country_id, region_id and town_id.

The idea is that this will handle situations where different countries have towns with the same name since the chances they will be in the same region also is very unlikely.

Using ID's for the "relationships" means that if the name of a country is found to be spelled incorrectly, it can be changed without affecting the location table.

I'm new to this sort of database design and would welcome any comments on this design and any pointers to articles on similar designs. I am sure there are probably better ways to do it but I haven't been able to think of any yet.

The SQL for that tables and some INSERT's for data to test them follow:

Code:

CREATE TABLE `countries` (
  `country_id` INT(11) NOT NULL AUTO_INCREMENT,
  `country` TEXT NULL,
  PRIMARY KEY(`country_id`)
); 
INSERT INTO `countries` ( `country_id` , `country` ) 
   VALUES ('1', 'United Kingdom'),
              ('2', 'United States');
CREATE TABLE `regions` (
  `region_id` INT(11) NOT NULL AUTO_INCREMENT,
  `region` TEXT NULL,
  PRIMARY KEY(`region_id`)
);
INSERT INTO `regions` ( `region_id` , `region` ) 
  VALUES ('1', 'South West'),
             ('2', 'South Central'),
	     ('3', 'South East'),
	      ('4', 'Midlands'),
	      ('5', 'North West'),
	      ('6', 'North East');
CREATE TABLE `towns` (
  `town_id` INT(11) NOT NULL AUTO_INCREMENT,
  `town` TEXT NULL,
  PRIMARY KEY(`town_id`)
);
INSERT INTO `towns` ( `town_id` , `town` ) 
  VALUES ('1', 'Southampton'),
             ('2', 'Portsmouth'),
             ('3', 'Winchester'),
             ('4', 'Salisbury'),
             ('5', 'Bournemouth'),
             ('6', 'Basingstoke');
CREATE TABLE `locations` (
  `country_id` INT(11) NOT NULL,
  `region_id` INT(11) NOT NULL,
  `town_id` INT(11) NOT NULL,
);
INSERT INTO `locations` (`location_id` , `country_id` ,`region_id` , `town_id` ) 
VALUES ('', '1', '2', '1'),
	   ('','1','2', '2'),	         
	   ('','1','2', '3'),
	   ('','1','2', '4'),
	   ('','1','2', '5'),
	   ('','1','2', '6');

The problem I see with this is that if I wish to find out what regions exist in each country by querying the locations table with a query like:

Code:

SELECT c.country, c.country_id, r.region, r.region_id
FROM locations AS l
LEFT JOIN countries AS c ON ( l.country_id = c.country_id )
LEFT JOIN regions AS r ON ( l.region_id = r.region_id )

I get a result with many duplicates such as:

Code:

country            country_id region            region_id
United Kingdom 	            1 South Central            2
United Kingdom 	            1 South Central 	       2
United Kingdom 	            1 South Central 	       2
United Kingdom 	            1 South Central 	       2
United Kingdom 	            1 South Central 	       2
United Kingdom 	            1 South Central 	       2

So if I want to generate a dependant set of HTML selection lists for an HTML form that allows me to enter a new town for instance, I currently have to remove all of the duplicate rows either in PHP or javascript. Is there some way in MySQL that I can construct a query that will ignore the towns column of the locations table and just give me the unique combinations of country and region?

Many TIA to anyone who can offer help.

Nick.

Pat Phelan · #2 (**permalink**) 01-04-05, 22:21

I'd suggest using DISTINCT

-PatP

rajiravi · #3 (**permalink**) 01-05-05, 05:34

Why not use a natural join or Inner Join? I am not sure, but I think the Left Join creates a Cartesian product.

You should not get any duplicates with a properly constructed query even without the distinct clause.

Ravi

andrewst · #4 (**permalink**) 01-05-05, 05:53

Your data model is wrong: it allows (forces) every country to have the same regions: South West, South East, Midlands, etc.

In reality, the UK has a "Midlands", but the US does not.

You need your regions table to be country-dependent:

Code:

CREATE TABLE regions (
  region_id INT(11) NOT NULL AUTO_INCREMENT,
  region TEXT NULL,
  PRIMARY KEY(region_id),
  country_id REFERENCES countries
);

INSERT INTO `regions` ( `region_id` , `region`, `country_id` ) 
  VALUES ('1', 'South West',1),
             ('2', 'South Central',1),
	     ('3', 'South East',1),
	      ('4', 'Midlands',1),
	      ('5', 'North West',1),
	      ('6', 'North East',1);

Now your query becomes:

Code:

SELECT c.country, c.country_id, r.region, r.region_id
FROM countries AS c
LEFT JOIN regions AS r ON ( r.country_id = c.country_id )

certus · #5 (**permalink**) 01-05-05, 21:45

And I was going to tell him to draw a data model

Vmusic · #6 (**permalink**) 01-08-05, 00:03

Hi Nick,
WOW.... You're going gangbusters on the normalization there. I applaud you.

Think about your objective here, and what solution and options you have. You stated you want people 'nearby' locations in similar interests...
What does nearby mean?

How are you populating your data? Are the users defining a county? a region?

Some suggestions
Lets assume that each country has a limited number of regions / province / state. You don't care if two different countries have a province or state with the same name.

HOWEVER..... you do care if two regions / province / state - each from different countries are close to each other. For example, someone from Northern France could be close to someone from Belgium, right across the border. So you have a need to relate certain regions / province / state from one country to those from another.

ANOTHER issue is the hierarchy and terminology of the 'sub geographies' of a country. Some countries have 'states', 'provinces', or 'regions'. I'll assume that you'll only go one level deep? A province could have its own 'sub geographies' , in the U.S. each 'state' has its own counties.

Then there's towns...OH.. now you're adding a 'many to many' in the mix. Some cities/towns span over more than ONE region / province / state. Not many do this... but you have a design consideration. I assumed a city is within one and only geographical place (region / province / state)

Nick...as a matter of fact, every entitiy mentioned is in a way, itself a geographical boundaried place. You could model ONE SINGLE ENTITY CALLED 'Place' and created a recursive relationship to it. ( I didn't do that)

WHATEVER YOU DO
I might suggest placing a status on the entity you use to track region/province/state and city/town - IF you allow your users to insert data into these tables, the data will be suspect. Placing a status on it can allow you to filter on the rows whose state is 'unapproved'. Until someone looks it up on a map and validates they entered the right data.

You could probably..... at the first two levels..... country and region/province/state ..... get some data for many countries.

Well I threw together a 'logical' model.

It's a good start.

Vmusic

certus · #7 (**permalink**) 01-09-05, 00:59

Here's an example that deals with the parent child hierarchy and the place neighbors proximity. It also allows for duplicate place names. Note also that the Natural Keys have unique indexes to prevent duplication.

Vmusic · #8 (**permalink**) 01-09-05, 01:23

Certus,
Nice Job!!!

The neighbor and place association are abstractly the same.
When you relate one place to another, you are relating it as either a 'parent-child' for example country to a province or state, in this case the relationship or association is hierarchal or 'parent-child'............or.............
In the case of 'neighboring' places, they could still be captured in your association entity, if need be. The relationship here isn't hierarchical, just a straight many to many.

The many-to-many of an entity to itself is the most flexible deisgn, since it allows for both hierarchical and many-to-many relationships. Many times in these relationships you'll see a 'nature' or 'purpose' or 'description' attribute that captures the nature of the relationship.

Excellent!!!
Vmusic

certus · #9 (**permalink**) 01-10-05, 00:33

Here's the simpler approach raised by Vmusic

Internet Services

Web Development

Digital Marketing

Consumer Tech