如何最好地设计任何SQL数据库中的地址位置？ [英] How to best design address locations in any SQL Database?

问题描述

概述

我正在为加利福尼亚州的一些紧急服务报告和映射应用程序（有些怪异，考虑到那里的火灾，马上...）。我们需要映射内部政府部门的人口统计数据和紧急数据。

我们在加利福尼亚州的街道，城市和社区都是。每个社区也有相关的shapefile（lat long，定义它的边界）。这是由美国人口普查局（所有公共领域的东西）网站给我们的。

问题

我不知道如何最好地设计数据库表。我们还没有被告知我们需要使用什么类型的数据库，所以如果有帮助，我们可以接受建议。我们有MS SQL 2005和2008（和08年的空间资料）的经验。

我们可以拥有以下合法的数据。

• 街市，州，州


• 州，州


• 邻里，州


• 状态

州是合法位置的原因是因为我们被告知可能会被出售对于其他州，所以我们现在需要进行规划。

所以，原来我想到了...

• LocationId INTEGER PK身份


• 街道NVARCHAR（100）


• 邻里NVARCHAR（100）


• City NVARCHAR（100）


• 状态NVARCHAR（100）


• 纬度VARCHAR（15）


• 经度VARCHAR（15）


• Shapefile

可空，btw。但过了短短的一段时间，我认为在这些领域拥有如此多的加利福尼亚文本或圣地亚哥文本是浪费的。所以我通过将邻居，城市和州域作为自己的新表（例如，查找）的外键，将表更改为正常化，而这两个字段现在是NULLABLE。

所以..所有工作正常。除非我尝试并对它们做一些Sql语句。由于可以使用NULLABLE FK，所以这些外连接查询是一个噩梦：（

如何使用主表，子查找表（如邻居，城市和国家）通过ID链接，然后将所有这一切放在一个视图中？记住，NeighborhoodID和CitiyID将是NULLABLE .. ???

我只想看到人们的对此的想法和原因他们提出了他们的建议，我真的担心和困惑，但渴望学习。

请帮助！

---

编辑1：我需要坚持使用RDBMS数据库。

$ b $编辑2：我正在考虑使用约束来执行单个表（取消归一化），以保持主表上的不可用OR或多表与可空FK的总和（例如，位置（主表） ，街区，城市，国家...归一化数据库模式）。

编辑3：向样本添加城市，第二个列表。

编辑4：添加视图问题。

解决方案

以示例：

• 街市，州，州


• 州，州


• 邻里，州


• 状态

首先回到基本原则，所有上述都是不同的地理空间实体，所以你地址由一个名称和一个或多个地理空间说明符组成。这告诉我们，我们真的应该将它们存储在一个表中。这里的关键是更抽象地思考数据，所以你的地址表需要与另一个表有1个关系，称为address_entities，如下所示：

p>

• int ID


• varchar（）name


• varchar ）类型


• int parentID


• 地理位置。


• int parentID

这意味着你显然需要一个表来将地址链接到上面的地址实体表。现在，每个地理空间实体都是固有的层次结构，而且它使SQL变得更加困难，而且我本人试图避免引用自己的表，有时候它是一个很好的解决方案，这就是其中之一。

尽管它使代码更加困难，但这样做的好处是巨大的，从长远来看是值得的。

此外，即使isn没有立即的要求，全球考虑，并不是世界上所有的地址都有街道或州，例如在法国，一个有效的地址可能是

   -  la Maison des Fou 
  -  24500 Eymet 
  

所以，熊在设计模式时要记住。

Overview

I'm working on some Emergency Services reporting and mapping application for California (kind of weird, considering the fires there, right now...). We need to map demographic and emergency data for an internal govt unit.

What we have are all the streets, cities and neighborhoods in California. Each neighborhood also has it's relevant shapefile (lat long that defines it's boundaries). This was given to us by the US Census board (all public domain stuff) website.

Problem

I'm not sure how to best design the DB tables. We haven't been told what type of DB we need to use .. so we're open to suggestions if that helps. We have experience with MS SQL 2005 and 2008 (and the spatial stuff in '08).

We can have the following legit data.

• Street, City, State
• City, State
• Neighborhood, State
• State

The reason why State is a legit location is because we're told this might be sold to other states, so we need to plan for that now.

So, originally, i thought of this...

• LocationId INTEGER PK Identity
• Street NVARCHAR(100)
• Neighbourhood NVARCHAR(100)
• City NVARCHAR(100)
• State NVARCHAR(100)
• Latitude VARCHAR(15)
• Longitude VARCHAR(15)
• Shapefile

None of those are nullable, btw. But after a short while, i thought that it was a waste to have so many 'California' text or 'San Diego' text in the fields. So i changed the table to be more normalised by making the Neighborhood, City and State fields a foreign key to their own new table (eg. lookups) .. and those two fields are now NULLABLE.

So .. that all works fine. except when i try and do some Sql statements on them. Because of the NULLABLE FK's, it's a nightmare to make all these outer join queries :(

What about having the main table, the sub-lookup tables (eg. Neighbourhoods, Cities and States) linked via ID's and then place all this in a view? Remember, NeighborhoodID and CitiyID would be NULLABLE.. ???

I just want to see people's thoughts on this and the reasons they made their suggestions, please. I'm really worried and confused but are eager to learn.

Please help!

---

edit 1: I need to stick to an RDBMS Database.

edit 2: I'm thinking about going a single table (de-normalized) with constraints to keep the sum of the fields unqiue OR multi-tables with nullable FK's on the main table (eg. Locations (main table), Neighborhoods, Cities, States ... normalized db schema).

edit 3: Added City to the sample, second list.

edit 4: Added view question.

解决方案

Taking the example:

• Street, City, State
• City, State
• Neighborhood, State
• State

Firstly go back to basic principles, all of the above are distinct geospatial entities, so your address is composed of a name, and one or many geospatial specifiers. This tells us that we really should be storing them in a single table. The key here is to think of the data more abstractly,

So your address table needs a 1-many relationship to another table, called address_entities which is as follows:

• int ID
• varchar() name
• varchar() type
• int parentID
• geography position.
• int parentID

This means that you will obviously need a table to link the address to the address entity table above. Now, each geospatial entity is inherently hierarchical, and whilst it makes the SQL harder, and personally I try to avoid self referencing tables there are times when it is a good solution and this is one of them.

The benefits are huge, even though it makes the code harder, it is worth it in the long run.

Also, even when it isn't an immediate requirement, think globally, not all addresses in the world have a street, or state, for example,in france a valid address could be

- la Maison des Fou
- 24500 Eymet

So, bear that in mind when designing schemas.

这篇关于如何最好地设计任何SQL数据库中的地址位置？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！