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]