My office just received a database from outside the office that I'm going to have to build a web tool around. The tool will be fairly simple - you use a web form to specify criteria (using checkboxes) and when you submit the form you get back a list of matching resources.

There are four categories of criteria, and each criteria contains a narrowing set of values (ex areas would be: state then country then city then block, etc)

The problem is that the people who set up this database made a separate table for each criteria, and as such the resources are duplicated. What's worse is that one of the criteria is just a column in all tables, so you can even have resources repeated in the same table.

Example rows from the areas table.


resource topic state county city block
resource 1 topic 1 Y N N N
resource 1 topic 2 Y Y N N

etc.

Normally I would put the resources in a table with resource name and ID (pk), then a table for topics, a table for criteria, etc, then make relational tables to do a many to many relationship, but I'm having trouble getting my head around things. For one, resource 1 can have different area values depending on what topic is selected. Same for the other criteria. Just looking for the best way to organize it.