I’m not saying they shouldn’t exist in any capacity, but they seem to count double (or triple or quadruple) in challenges, leaderboards and I assume my total road count. It sort of makes those numbers largely meaningless if in some places you run a road and it counts 4 times.
I assume as it stands you have multiple “Street A”'s that are really the same street but with different street_ids instead of just one “Street A” that you associate to multiple towns with some sort of completed flag. Anyways I have no idea how your database is setup (just a guess) and I’m sure you’re better at it than me, but basically contrasting the two setups below. Both setups allow for “Street A” to belong to more than one city but one setup doesn’t duplicate roads.
Do you actually know what smaller cities exist in larger cities? Is that relationship stored or easily figured out? I would assume that would let you dedupe, but if not then something like street length might assist
streets
street_name, street_id, completed
“Street A”, 1, false
cities
city_name, city_id
“City A”, 1
“City B”, 2
“City B”, 3
city_streets
city_id, street_id
1, 1
2, 1
3, 1
streets
street_name, street_id, completed
“Street A”, 1, false
“Street A”, 2, false
“Street A”, 3, false
cities
city_name, city_id
“City A”, 1
“City B”, 2
“City B”, 3
city_streets
city_id, street_id
1, 1
2, 2
3, 3