Moak -4 years ago 53
MySQL Question

# DB Design regarding intersections

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.

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.

``````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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download