this question may asked by several people.But still I didn't get a right answer. So here it is
I want to load Continents, Countries and Cities to below combo boxes. So I need to make relation between those. Means city need be in a country and country belongs to a continent. So of course I am talking about a tree structure. So how should I implement this in MYSQL database in efficient way? How many tables need? How to do the relation among those?
Here is the image.
There are formal ways to represent arbitrary trees, but I think the following is simpler and should be sufficient:
CREATE TABLE Continents (
id INT AUTO_INCREMENT NOT NULL,
name VARCHAR(20) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
)
CREATE TABLE Countries (
id INT AUTO_INCREMENT NOT NULL,
name VARCHAR(20) NOT NULL,
continent INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (continent) REFERENCES Continents(id),
UNIQUE (name)
)
CREATE TABLE Cities (
id INT AUTO_INCREMENT NOT NULL,
name VARCHAR(20) NOT NULL,
country INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (country) REFERENCES Countries(id),
UNIQUE (name)
)
I didn't test the code, so there may be some syntax errors. I hope the intent is clear, though.
@Vmai raises an excellent point in his comment on the question.
My solution would be to have the "problem countries" once in the Countries table for every continent they are in. (So Turkey would be twice in the database, once with continent set to the id of Asia, and once to the id of Europe. Same for Russia.) The same goes for cities, of course.
Modified Preorder Tree Traversal. With formal I mean these ways have been mathematically proven and researched.All you need is one table. As long as the structure of data is the same, there's no reason to have three tables. And this way, you don't care about the depth and how the locations administratively are organized.
+-------------------------+
| locations |
+-------------------------+
| location_id (int) | primary key
| location_name (varchar) |
| parent_id (int) | index
+-------------------------+
Or another solution
+-------------------------+
| locations |
+-------------------------+
| location_id (int) | primary key
| location_name (varchar) |
| left (int) |
| right (int) |
+-------------------------+
To be explained...
