1

I have trouble with database design and don't know how to overcome this. The problem is with list of cities between two chosen city. User chooses start and finish city, and OPTIONALLY, user can write which cities between he will pass, so it can be 0,1,...N. How can I store this in my database ? I already have table with list of States and another table with list of cities.

I thought a bit, and if there is no better option, I will have row in which I will store IDs of cities in format "ID1,ID2,ID3,..." and in software I will separate IDs, but I think this is not normal.

Improve this question
1
  • 1
    "ID1, ID2..." = recipe for disaster. Have tables City, User and Route. There should then be a joining table RouteCity with all of the cities for a given route - with a RouteCityNo = 1...N for the given cities on that User's chosen route between those two cities. This is just off the top of my head - can't test. You could set up test data on SQLFiddle or and see if this schema (idea) comes close to what you're looking for. Reason why it's a disaster is that it breaches Codd's 1st rule about repeating values. Data in an RDBMS should be atomic - i.e. not decomposable.
    – Vérace
    Commented May 30, 2014 at 10:46

3 Answers 3

Reset to default
1

You should create another table with three column

  • Start City [PK]
  • End City [PK]
  • Midway City [PK]

you have triple primary key now if you have city_start and city_finish and for example 4 cities between city_start and city_finish then you should add 4 rows to you table like this

(city_start,city_finish,midway_city_one)
(city_start,city_finish,midway_city_two)
(city_start,city_finish,midway_city_three)
(city_start,city_finish,midway_city_four)

now you know that from city_start to city_finish you will visit midway_city_one

Improve this answer
1
  • But, how do I use this table in my first table (let's call it tblA). I already have columns : user, start_city,end_city. Where do I fit informations about midway cities, maybe I visit some of those cities and maybe I go directly from start to end ?
    – BTestQ
    Commented May 30, 2014 at 11:55
3

You should keep your waypoints (midway cities) in a separate list, with the trip key and the sequence of the stop as the primary keys. Consider the following ERD:

ERD

This lets you keep the initial origin and the final destination in your trip table and it lets you add zero or more waypoints along the way without storing any redundant data (i.e. the schema is in third normal form).

Improve this answer
0

Have you considered table with parent-child relation? i.e.

Parent   Child
city1 -> city2
city1 -> city3
city2 -> city3
city3 -> city4

so if you wish to visit city 4, you have several ways: you may travel 

city1 -> city2 -> city3 -> city4

you may travel

city1 -> city3 -> city4

or 

city3 -> city4

or 

city2 -> city3 -> city4

In Oracle you can get the whole set of chains using connect by ... prior statement, and I suppose there's something similar in other DBMS. Also you can avoid undesirable chain with filter. Oh, and also you can choose the first and the last city in your query.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.