Just been using mysql for a couple of days, so this is possibly old ground,
but i have searched and can't find any solution. i'm sure it must have come
up before.
The project is to allow resources to be shared amongst community groups
throughout uk. there are a number of attributes that can be searched on, but
the one i'm having difficulty with is geographical area.
areas can also be subareas of other areas. for instance there could be areas
"england", "wales", "newtown", "london", "lambeth", "southwark" so if someone
searches "england" then intuitively the subareas should be searched as
well...
i thought of a possible method, but its very weird and i'd appreciate
comments on it or suggestions for different ways.
area_id varchar(6);
"england" - "A"
"wales" - "B"
"newtown" - "BA"
"london" - "AA"
"lambeth" - "AAA"
"southwark" - "AAB"
and then searches could be as precise as required ...
another option, from a friend more experienced than me is:
"have extra columns for
country/county/borough/town/street/etc
your front end for querying would eitehr present the options/boxes for
searching within the country/town etc. or you could just present one
search box, which takes the search criterion, and checks each of the
different columns in turn, starting with the largest geographical type,
the country.
have aggregated indexes (country+country+town) wont get you anything, so
dont bother with that.
so to summise
create table locations
(
area_id serial,
country char(16),
country varchar(64),
borough varchar(64),
town varchar(64)
);
the problem wit that table layout, is you have no way of checking if for
twwo entries with different spelling/case. e.g. London/london. the
data values arent strongly checked.
"
cheers in advance.
=====
--
ae
"I give mankind no more than a chance in a thousand. But I would not be human if I did
not place my stake on this one chance." Albert Camus
http://www.sei.ukshells.co.uk/
___________________________________________________________ALL-NEW Yahoo! Messenger -
all new features - even more fun! http://uk.messenger.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
