DB Design question regarding intersections

Question

Of these two designs for a crossroad database

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

VS

#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.

Answer 1

The second solution is a little more flexible for additions to either crossings or streets while keeping the relationship between them in its proper context. It's a subtle distinction, but worth mentioning.

Answer 2

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