Quick DB question

dulpit

I want to have a table in a database that records the distances between various towns. The way i'm thinking of it in my head is something like this:

TownA TownB TownC TownD
TownA x 12 11 10
TownB 12 x 11 10
TownC 11 11 x 10
TownD 10 10 10 x

Now while this looks good, if i need to update the table things could get awkward. If i add a new town i'll need to add a new column to table first, update all the records with new data and then add a new row...

Is this a plausible method? Or would it be a killer on speeds of the application??? Any pointers?

bonkey

From a database-structure perspective, you're better with a generic structure which is :

Town A / Town B / Distance

With this, you can then build any size structure.

The downside is that you need to "pivot" your returned data to give you the structure you describe.

ron_darrell

Hi,

I was looking for a similar solution a while back and ran into the same problem you're experiencing. A relational database may not be the best way to do this was the result I ended up with. I toyed with creating a node tree list in C# or VB.Net but that may not be an option for you and would be just as data-entry intensive as the db option but would not require a rewrite of the table structure and an entry on the new column for each row.

In the end I walked away from it due to lack of resources and time. Chances are that someone may already have created this data structure and it may be available online (free or for purchase). Have you checked?

The other option I came up with may also be unhelpful to you. I used the Microsoft Virtual Eart SDK which allows you to input two places (by name of GPS co-ordinate) and it estimates the distance between them (by two criteria shortest distance and shorest travel time). It's not 100% accurate and not all places in Ireland are in their dataset however for most big towns and villages in Ireland it works more than well. If you would like the code I developed to do this please PM me and I will send it on (all I will require is copyright notice on any sites you use the code on)

-RD

Dr Pepper

Initial suggestion: (a better one will probably emerge with further thought/discussion)
Have one table called town with towns in it (e.g) have an id column and a name column.

Have another table called towndistance with columns townid1, townid2 and distance.

edit/ beaten to it as usual

dulpit

bonkey wrote:

From a database-structure perspective, you're better with a generic structure which is :

Town A / Town B / Distance

With this, you can then build any size structure.

The downside is that you need to "pivot" your returned data to give you the structure you describe.

That makes way more sense, nice one... How come i didn't think of that myself?:o

bonkey

Bear in mind, I did say you'll need to "pivot" the data, turning values of the second town column into a row of towns.

There's a number of ways of doing this, and to be honest, which you go for is dependant on what you want to do,

If you're using MSSQL 2005, for example, you can check out the PIVOT command (introduced in that version, so if you're on an earlier version of MSSQL, or usnig a different DBMS, you're more than likely SOL).

Alternately, with any given programming language, building a 2-dimensional array shouldn't tax anyone's programming skills, so turning this datastructure into the one you originally imagined isn't that hard.

From a data-entry perspective, things get more interesting.

While its relatively easy to imagine a 2-d array being transformed into a Grid / Table, with facilities to add row / add column, you also need to put some thought into things so that when a grid is saved, you don't just junk your entire database and rewrite every record.

So its not like there's nothing left to be done...but the schema both I and Dr.Pepper suggested is the conventional RDBMS one.