关于交叉点的数据库设计

发布于 2024-09-29 12:21:23 字数 496 浏览 1 评论 0原文

十字路口数据库的这两种设计

#Street
street_id | street_nm
#Crossing
crossing_id | x | y | street_id_1 | street_id_2

#Street
street_id | street_nm
#Crossing
crossing_id | x | y 
#street crossing relationship
street_id | crossing_id

假设每个十字路口只有两条道路，是否有理由使用第一个解决方案而不是第一个？

编辑：对于第二个设置，我如何创建一个结果看起来像这样的视图

crossing_id| x | y | street_nm_1 | street_nm_1

此外，我不确定创建具有三条道路的交汇处会如何影响视图。

原文

Of these two designs for a crossroad database

#Street
street_id | street_nm
#Crossing
crossing_id | x | y | street_id_1 | street_id_2

#Street
street_id | street_nm
#Crossing
crossing_id | x | y 
#street crossing relationship
street_id | crossing_id

Assuming every crossing has only exactly two roads, is there a reason why one would use the first solution over the first?

EDIT: For the second setup, how could I create a view where results look like this

crossing_id| x | y | street_nm_1 | street_nm_1

Also I'm not sure how creating a junction with three roads would effect the view.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

弱骨蛰伏 2024-10-06 12:21:23

我更喜欢第二个。

首先，“假设每个十字路口只有两条路”是相当危险的。一般来说，在设计时我不喜欢依赖与现实相冲突的假设，因为迟早你的设计将不得不适应“额外的情况”。

但第二种设计更好是出于另一个原因......假设您想设计一个查询，返回所有穿过道路“X”的道路（我认为这是一个非常常见的要求），您的第一个设计迫使您测试道路“ Street_id_1 和 street_id_2 中都有 X" id - 一般来说，查询更加复杂，因为每当您查找给定道路时，您不知道它是否会在 id_1 或 id_2 中列出。

“x 交叉 y”的关系应该是对称的（除非你想区分“主干道”和“支流”，这里似乎不是这样）所以第二种设计更接近意图。

关于您关于视图的问题...怎么样：

Select a.cross_id,a.x,a.y,b.street_nm,c.street_nm 
from crossing a, crossing_rel e, street b, street c 
where b.street_id=e.street_id and
      c.street_id=e.street_id and
      a.crossing_id=e.crossing_id and
      b.street <> c.street

请注意，这不会给出任何特定的顺序，哪条街道显示为“x”，哪条街道显示为“y”...也许您会更喜欢类似的内容：

Select a.cross_id,a.x,a.y,b.street_nm,c.street_nm 
from crossing a, crossing_rel e, street b, street c 
where b.street_id=e.street_id and
      c.street_id=e.street_id and
      a.crossing_id=e.crossing_id and
      b.street_nm < c.street_nm

I'd prefer the second.

First of all "assuming every crossing has only exactly two roads" is quite risky. In general, when designing I prefer not to rely on assumptions that clash with reality because sooner or later your design will have to accomodate for "extra cases".

But the second design is better for another reason... assuming you want to design a query that returns all roads that cross road "X" (which I suppose would be a pretty common requirement) your first design forces you to test for road "X" id both in street_id_1 and street_id_2 - in general, the queries are more convoluted because whenever you are looking for a given road, you don't know if it will be listed in id_1 or id_2.

The relationship "x crosses y" should be symmetrical (unless you want to distinguish between "main roads" and "tributaries", which does not seem to be the case here) so the second design is closer to the intent.

Regarding your question about the view... what about:

Select a.cross_id,a.x,a.y,b.street_nm,c.street_nm 
from crossing a, crossing_rel e, street b, street c 
where b.street_id=e.street_id and
      c.street_id=e.street_id and
      a.crossing_id=e.crossing_id and
      b.street <> c.street

note that this will not give any specific order to which street appears as "x" and which as "y"... maybe you will prefer something like:

Select a.cross_id,a.x,a.y,b.street_nm,c.street_nm 
from crossing a, crossing_rel e, street b, street c 
where b.street_id=e.street_id and
      c.street_id=e.street_id and
      a.crossing_id=e.crossing_id and
      b.street_nm < c.street_nm

回复收藏 0 原文