CODEWITHSUNDEEP
sqldatabasedatabase-designdatatablenormalization
Sahil
Sahil

Reputation: 137

Design a database table using this data

Hi I just want to sort out these fields to a table. But I'm having trouble.

I have details of cities.

These are main city detail examples. 

CityName CityID 
Colombo   001
Kandy     002

Kandy is Directly connected to these cities

 CityName    CityID DistancefromKandy
 Katugastota 006    1km 
 Peadeniya   008    2km

I want to store distance between every city. As an example Katugastota to Peradeniya, Colombo to Katugastota, Colombo to Kandy and Kandy To Peradeniya too. And for a singe city I want to store what are the directly connected cities and the distance to those cities. How to sort this data to tables..?Any ideas.. I have given the table structure I tried but I cant add the distance between each city in to that and the directly connected cities and distance to directly connected cities in this.. Appreciate any help in this.. I just don't need the sql, if someone can suggest a better table design that would be a great help.

Upvotes: 0

Views: 169

Answers (1)

László Koller
László Koller

Reputation: 1159

Like @EvilEpidemic suggested above, my first choice would be to store coordinates (latitude & longitude) for each city and calculate the distances between them.

That said, if you need to store your pre-calculated distances for specific pairs of cities, then you may want to try the following:

Add a table that includes two (2) CityID columns (for example, SourceCityId and DestinationCityId) as well as a NOT NULL distance column (of a numeric data type).

For example, in SQL Server you might have a table like (this oversimplified example assumes you store distances as int kilometers, but feel free to change the data type as needed):

CREATE TABLE Distances (
    [SourceCityId] NOT NULL int,
    [DestinationCityId] NOT NULL int,
    [DistanceInKilometers] NOT NULL int,
    CONSTRAINT [PK_Distances] PRIMARY KEY CLUSTERED (
        [SourceCityId] ASC,
        [DestinationCityId] ASC
    )
)

Upvotes: 1

Related Questions