|
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.
|
