综合技术

Keep tree data in SQL

I am aiming to store locations in a table but as well as storing one location, I also want to link it to another.

Example:With a value of "Palm Beach" I also want to link this to "Florida" and "USA" to make the address:

Palm Beach, Florida, USA

Idea Number 1One table to hold either a contry, city, town etc, but have it link to "parents".

CREATE TABLE location {
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
parentid
FOREIGN KEY parentid REFERENCES Employee (EmployeeID),
name VARCHAR (100) NOT NULL UNIQUE,
loc VARCHAR (17) NOT NULL,
rad VARCHAR (17),
added DATETIME DEFAULT NOT NULL
};

Idea Number 2:The same system but with separate tables for the levels

CREATE TABLE locality (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR (100) NOT NULL UNIQUE,
loc VARCHAR (17) NOT NULL,
rad VARCHAR (17),
added DATETIME DEFAULT NOT NULL,
FOREIGN KEY id REFERENCES administrative_area_level_1 (administrative_area_level_1),
};

CREATE TABLE administrative_area_level_1 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR (100) NOT NULL UNIQUE,
loc VARCHAR (17) NOT NULL,
rad VARCHAR (17),
added DATETIME DEFAULT NOT NULL,
FOREIGN KEY id REFERENCES administrative_area_level_2 (administrative_area_level_2),

);

CREATE TABLE administrative_area_level_2 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR (100) NOT NULL UNIQUE,
loc VARCHAR (17) NOT NULL,
rad VARCHAR (17),
added DATETIME DEFAULT NOT NULL
);

I really want to be able to get out the entire structure from one starting point, for example for a search of "Palm Beach" I want to be able to retrieve that it is from the USA.

Can anyone give me some input on the best approach to this please?

EDIT: I think this is the structure I want to use: https://stackoverflow.com/a/317536/1738522

The short answer to this is that I’ve addressed this exact issue (geographical regions arranged in a hierarchy) using a Nested Set model ( More info available at http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
)

The longer answer is that you should not create a separate table for each level as that is not extendable to "n" levels easily and there is no simple route from level n to level 1. For this reason, idea #1 is the one you should pursue and elaborate on.

If ever in doubt, just sit back and look at how much you are duplicating a table structure. If you have totally duplicated a table structure then they should not (usually) be separate tables, as the duplication indicates you are storing the same data. Instead work out how to store it in a single table.

Within my "regions" table I have the following columns (among others)

  • regionID
  • parentRegionID
  • regionName
  • lft
  • rgt

The regionID
and parentRegionID
are populate easily enough. I then use a stored procedure to generate lft
and rgt
values which are used as detailed within the article
I linked.

The benefit of storing the direct parentID is that you can more easily manipulate the tree. You don’t need to do this though, and could easily just store lft
and rgt
using procedures to add/move/remove tree nodes

The use of lft/rgt allows easy traversal/retrieval of parents/children all the way up and down the tree without having n joins onto itself.

展开阅读全文

微信扫一扫,分享到朋友圈

Keep tree data in SQL
0

Xamarin: Xamarin Podcast: Xamarin.Forms 4 And So Much More!

上一篇

Validate the item Geojson

下一篇

你也可能喜欢

评论已经被关闭。

插入图片
Keep tree data in SQL

长按储存图像,分享给朋友