[Resolved] Multiple tables wtih cross reference and multiple parent child relations

thedream

Hi there,

Here is my problem:
I have a principal table named "phototek" and my goal is to have the ability to classify the pictures with a lot of paramaters.
The problem is on the "categorie"; what I want is, a picture get a unique category. Each category have a "continents" sub category, each "continents" have a "countries" sub-category and finally, each "country" have a "cities" sub-category. It is clear that a continent have several countries, that each countries have several cities.
So what is the strategy building my tables around the principal one "phototek" to do not have a circular relation between others tables, and how can I do, to assiociate one image and the caracteristics of the continents-countries-cities?
It is clear that 1 image have a unique ID, and a unique place in the continents-countries-cities, but 1 citiy could have several image, that several cities have only 1 country, etc....

How do I do this things?

Please help me....

Marc😕

ultraslacker

Several directions to go with this - if a photo's city is not always known, you could have a generic place table. Otherwise, you could have three tables for location, and each photo gets a city_id. Each city gets a country_id, and each country gets a continent_id.

place
id
type (continent, country, city)
parent (id of parent)
name

phototek
id
place_id

thedream

Thank you very much, ultraslacker.

An obsur point is coming to me wtih your explanation:

what dio you mean by "parent" in the "place" table.

If I understood your explanation, my tables could be like this:

city table:
cityID | city_name| countryID

country table:
countryID | country_name | continentID

continent table:
continentID | continent_name

Is that right?
And the query could be:
Select city., country., continent.* FROM city, country, continent WHERE continent.continentID= country.continentID etc....

But to be more explicit here is a table tree:

image1
- general category 1
--- category 3
------ continent 2
--------- country 3
------------ city 4

image 2
- general category 3 (it's an obligation to have it)
--- category 5 (it's an obligation to have it)
------ none (no continent, country and city, it's optionnal)

image 3
- general category 1 (look with image 1)
--- category 2
------ continent 2
--------- country 3
------------ city 5

the caracteristics of image are in the "phototek" table.
my problem is how to do that by the way , for exemple, if I choose a continent it give me automaticaly all the countries and the same things with cities. AND the most important how can retrieve the result for an image, for exemple image 1, with the caracteristics of general category, category, continent, country and city?

Sincerely,

Marc
P.S: my brain is going to be liquified....

ultraslacker

Originally posted by thedream
Thank you very much, ultraslacker.

An obsur point is coming to me wtih your explanation:

what dio you mean by "parent" in the "place" table.

Parent is a reflexive key to create the hierarchy of continent->country->city. So the data could look like this

place
id 	|	parent_id		| 	type		|	name
1	|	0			|	cont		|	Asia
2	|	0			| 	cont		|	Europe
12	|	1			|	ctry		|	France
108	|	12			|	city		|	Paris

parent_id refers to the parent category.

If I understood your explanation, my tables could be like this:

city table:
cityID | city_name| countryID

country table:
countryID | country_name | continentID

continent table:
continentID | continent_name

Is that right?
And the query could be:
Select city., country., continent.* FROM city, country, continent WHERE continent.continentID= country.continentID etc....

But to be more explicit here is a table tree:

image1
- general category 1
--- category 3
------ continent 2
--------- country 3
------------ city 4

Since a city will have a country and a continent and these could be related elsewhere, there isn't a need to store continent and county in the images table, unless I'm missing something - if you store just the city, searching for say a continent's images is still possible, and you avoid redundant data.

my problem is how to do that by the way , for exemple, if I choose a continent it give me automaticaly all the countries and the same things with cities. AND the most important how can retrieve the result for an image, for exemple image 1, with the caracteristics of general category, category, continent, country and city?

One table for locale with reflexive parent_id or three tables for continent, country, and city will both allow to select countries per continent and cities per country.

thedream

thank you ultraslacker, it is more clear now, I am gonna try try this week end and I will tell you my results....

Sincerely, marc

thedream

But how do I link the image and the general category and the category, with the same self join?
Because some categories doesn't have a continent_country_city....

Marc