Database design - modelling relevance
Posted by: Evin Design
Date: August 21, 2007 04:41AM

I have a problem regarding the modelling of a database for a website that requires relevance in its search..

This is the scenario:

say we have a couple of tables consisting of cities in a country:

table 1: country
-------------
#cities
New York
L.A
Chicago
Boston
Miami

These are all in different positions(coodinates) in the country, what I want to do here is determine their posistion in relationship to eachother.

I E New york has a distance to miami but another distance to boston and a third to L.A and so on.

When I search this database using the phrase "New york" I want to be able to know the distances to all other cities in the table aswell.

I know of 2 ways of doing this:

1. Setting up a distances and match them in the search via a table as following:

Distances
----------
#New York #Boston etc.
boston 25 km Chicago 50 km
L.A 500 km Miami 300 km
Chicago 30 km L.A 700 km
and so on...

2. Setting up tables for each city as following:

New York
----------
#city #distances
Boston 25 km
L.A 500 km

and so on...

These two methods would work with only 5 cities or maybe a few more, but my real problem is that I have 290 "cities" in consideration for the database, how can I manage these and get their relevance to eachother in a easier way than having to make 290 tables with far too many posts in each table.

Options: ReplyQuote


Subject
Written By
Posted
Database design - modelling relevance
August 21, 2007 04:41AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.