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:
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:
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?