Mysql – Complex SQL query – need help

MySQL

I have a really hard SQL question! I've inherited a system that allows people to enter dog shows. I have inherited the database with its present architecture, and since there are office systems that rely on the database, I can't unfortunately change the architecture.

I have the following tables:

users
event
event_rules

The first two tables are self explanatory. The third table ('event_rules') contains the following columns:

event_id,
targetTable,
targetField,
allowableValues,
min_value,
max_value

The table basically records rules stating who can and can't enter a sports event. There may be more than one rule per event. So an example from the table might be:

__event_id__|__targetTable__|__targetField__|___allowableValues___|__min_value__| __max_value__|
      1     |     users     |      age      |                     |     12      |              |
      1     |     breed     |      name     | labrador, chihahua  |             |              |
      1     |     dog       |      age      |                     |             |      6       |

So for the above event, the rules are that the dog handler must be over 12 years old, the dog must be a labrador or chihahua and the dog must not be older than 6.

All the relations are in place, so for example, dog.breed_id=bred.id and so forth. I can pass in all required variables.

I want to know, for a given user, which events they can enter. My problem is this: it would be computationally huge to iterate each row in order to check which events can be entered for a given set of criteria. It would also involve (in my limited knowledge) evaluating the strings in 'targetTable' and 'targetTable' columns and then using the values as table and column names. Can anyone suggest an approach that might complete in a reasonable timeframe? The database is running on MySQL but could be changed to PostgresQL if required.

I'm afraid I can't alter the existing schema as the current systems rely on it, but I could add columns or tables where required.

Do any experts out there have any suggestions?

Thanks
Matt

Best Answer

Create one or more new tables that capture the data in event_rules in a more useable form. Then materialize those tables from the existing table. Without more details, I can't say exactly what those tables should look like. But here are two design problems that ought to be solved:

The column for allowable values violates first normal form, because it contains a list of values. Decompose the tables in order to achieve first normal form, and create the right indexes to speed up the join that will be required to recompose the data.

The column for target table contains metadata. In general, mixing metadata with data creates a confusing mess, unless you really know what you aere doing. Consider decomposing the event_rules table into separate tables for each target table. This looks complicated, but the resulting queries will be much much simpler, and probably faster.

Related Question