Creating a online, searchable database

[TriedandTested]

Where do I start!?

I've had some experience previously but that was using a MS Access - i'm sure they are better ways of doing it now!

I'm looking to create a searchable database of resources (with keywords attached to those resources so they come up if used) and also drop downs that can be used instead of, or alongside, the search function to filter the results.

e.g. if you selected the drop down as 'London' alongside a keyword search the results would only come up from those in that region, but if you just selected London in the drop down and clicked search all the London results would come up.

Hope that makes sense.

Example: http://www.emaonline.org.uk/ema/index.cfm?p=resources,res_search&item=eal

 

[jimmymctavish]

In terms of database design, I would start with 5 tables:

1. Locations (or whatever your drop downs are) - this contains the names of each location along with an index number.
2. Keywords - this contains every keyword you intend to use in your entire database along with an index number
3. Resources - this contains each "resource" name with a resource index number, and possibly links to any other info associated with that resource in other tables.
4. ResKeywords - contains 2 columns, Resource ID and Keyword ID. For each resource you have N rows, where each row contains the Resource ID and a Keyword ID for all N keywords associated with that resource.
5. ResLocations - as above but contains Resource ID and Location ID, associating each Resource with as many locations as it applies to.

Then you'll have to come up with some code to parse the user Keyword entry to individual words, which may be a standard function in whatever you choose to code it in, and then do a query to return all the entries in the Keywords table that match those words.

You will then have a list of Keyword IDs which you can use to filter your resources by using the ResKeywords table, and you can further filter by using the ResLocations table, all the time just working with the ID numbers, before finally using the Resource_Info table to get the info once you have got down to the entries you are interested in.

The above can probably be done in a single SQL query.

It might be easiest to try it out in Access first, use the query builder to generate your queries if you aren't confident in SQL and then think about porting to SQL server, or MySQL.

Then you have the joy of doing a web front end. I would probably do it in PHP with MySQL but that's just how I've always done it. I did do a lot in Access 2k at one time, but rarely touched ASP or SQL server.