I want to have people search for people by professional function, and limit that search to the areas in which the professional is willing to travel to/work in.
The areas are broken down into counties.
The professional can choose just one county - or up to 20 (possibly more - I haven't yet decided).
The professionals will be setting their own search meta terms. For example, a roofer might use [roofing, siding, windows, roofer, shingles, vinyl, aluminum, screen doors, gutters].
What I keep coming up with is creating one field with all the meta terms and having a separate table which lists one entry for each county the professional checks off.
So, User table would have:
UserID|UserName|MetaTerms|OtherPertinentStuff
The Locations table would have:
UserID|County
with an entry for each county the professional checked off.
If the professional said he is willing to travel to Passaic, Bergen and Morris counties in New Jersey for example, he would have three entries with his UserID in this table, each with a number for the county - which would be joined with a look-up table.
If he is willing to do the whole state, he can select that as well - or he can choose nationwide, in the case of Resume Writing, for example.
Each state would have a corresponding number in the look-up table, and nationwide (non location-specific) would have something like 99999.
And I would use a join on the tables to search for User.UserName, User.OtherPertinentStuff where Locations.County = {user's county} and MetaTerms Like %{user's query}%;
This doesn't seem terribly efficient, but I can't come up with anything better and I am far from a database engineer, so I was hoping for some guidance in working out something more efficient.
I hope I explained it well.
Thanks.