在层次结构中存储位置——如何克服?

This is my schema for storing locations:

这是我存储位置的模式:

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)
);

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)
);

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)
);

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

CREATE TABLE country (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR (100) NOT NULL UNIQUE,
iso VARCHAR (3) NOT NULL UNIQUE,
loc VARCHAR (255) NOT NULL,
rad VARCHAR (255)
);

Since they are a hieracy I want to link them together. For example:

因为它们是一种等级，我想把它们联系在一起。例如:

USA ----> country(id=1) 
Washington ----> administrative_area_level3(id=54), country(id=1)

My issue is with this is that if I try to daisy chain the tables like this:

我的问题是，如果我试图把这样的表格串起来:

locality -> administrative_area_level_1 -> administrative_area_level_2 -> administrative_area_level_3 etc

地区->行政管理- 1 ->行政管理- 2 ->行政管理。

Then adding an entry like "Washington, USA" before adding "USA" will cause USA to be added. This is from a google geocode but that original geocode will only gives me the co-ordinate values of Washington and not that of "USA" generally. I would need to make a second geocoding request for USA and add these values separately.

在加入“USA”之前，添加一个类似“美国华盛顿”的条目将会导致美国加入。这是来自谷歌的地理编码，但是原始的地理编码只会给我华盛顿的坐标值，而不是一般的“美国”。我需要对美国进行第二个地理编码请求并分别添加这些值。

My only option as far as I see is do something like this:

在我看来，我唯一的选择就是做这样的事情:

See if Washington is in table already
看看华盛顿是否已经就位。
Geocode Washington
项目点的华盛顿
Add results to administrative_area_level2 and country
将结果添加到行政区域和国家。
If country did not exist, geocode country and add "loc" coordinates
如果国家不存在，geocode国家和添加“loc”坐标。

Is there a better or smarter way of doing this?

有更好的或者更聪明的方法吗?

1 个解决方案

#1

Let me say right up front that if you're working with hierarchies in a relational database, you really want this book: http://www.amazon.com/Hierarchies-Smarties-Edition-Kaufmann-Management/dp/0123877334

让我先说一下，如果您正在处理关系数据库中的层次结构，您确实需要这本书:http://www.amazon.com/hierarchies-smares-edi-kaufmann - management/dp/0123877334。

Thinking about your Washington question, I'm wondering: Are you unhappy because your table structure forces you to add an entry for "USA" when all you really want is "Washington?"

考虑到你在华盛顿的问题，我在想:你是否不开心，因为你的餐桌结构迫使你为“美国”添加一个条目，而你真正想要的只是“华盛顿”?

I'm not 100% I can answer your question, but let me tell you how I approach these types of problems. I almost always model hierarchies in relational databases using paths. Instead of 5 tables, I'd have one called "locations," which would look something like this:

我不是100%可以回答你的问题，但是让我告诉你我是如何处理这些问题的。我几乎总是使用路径在关系数据库中建模层次结构。而不是5个表，我有一个叫“位置”，它看起来像这样:

create table locations (
    id varchar(10) not null primary key,
    path varchar(1000) not null unique,
    name varchar(40) not null,
    loc varchar(255) not null, -- from your model
    rad varchar(255) not null, -- from your model
);

"id" is a short identifier for each location, and "path" describes this location's position in the hierarchy in terms of its id and and ids of other locations.

“id”是每个位置的一个短标识符，“path”根据它的id和其他位置的id来描述该位置在层次结构中的位置。

So Washington would look like this:

所以华盛顿会是这样的:

id: wa
path: na/usa/wa
name: Washington

We're saying that Washington ("wa") is part of the USA ("usa") which is part of North America ("na").

我们说华盛顿(“wa”)是美国的一部分(“美国”)，它是北美的一部分(“na”)。

Now at this point you can choose whether you want to actually include rows for "usa" and "na" or not. You don't have to have rows for them in order to insert your "wa" row. You could either insert the rows immediately, or never insert them (and just have your code understand that "wa" can be part of "usa" without "usa" being fully defined), or not insert them right away and have some job periodically run and add rows for any locations that appear in paths but don't have their own rows.

现在，您可以选择是否要实际包含“usa”和“na”的行。为了插入“wa”行，您不必为它们设置行。你可以立即插入的行,或从未插入代码(只有明白“佤邦”的一部分,“美国”没有“美国”被完全定义),或不会马上插入他们,有一些工作定期运行并添加行任何位置出现在路径但不要有自己的行。

The Hierarchies book I reference above covers this path strategy in detail and includes lots of SQL code showing you how you can manipulate and browse the hierarchy.

上面我引用的层次结构书详细介绍了这一路径策略，并包含了许多SQL代码，其中显示了如何操作和浏览层次结构。

#1