4

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

Improve this question
3
  • 1
    I'd do this as dynamic sql. I'm not quite clear on the exact requirement you want in terms of "given a user". What are the exact input variables?
    – Darknight
    Commented Feb 24, 2012 at 16:14
  • 1
    Please display SHOW CREATE TABLE users\G, SHOW CREATE TABLE event\G, and SHOW CREATE TABLE event_rules\G in the question body.
    – RolandoMySQLDBA
    Commented Feb 24, 2012 at 17:39
  • If you represent the data correctly, you can form a series of JOINs using dynamic SQL to determine which rules apply. There is no way to know this unless you display SHOW CREATE TABLE users\G, SHOW CREATE TABLE event\G, and SHOW CREATE TABLE event_rules\G in the question body.
    – RolandoMySQLDBA
    Commented Feb 24, 2012 at 19:04

2 Answers 2

Reset to default
1

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.

Improve this answer
0

This isn't a good task for SQL. You need to:

AddEventToCollectionOfGoodEvents = True
For each EventCurrentlyAcceptingRegistration
    For Each EventRule
        If UserDoesNOTComplyWithRule
            AddEventToCollectionOfGoodEvents = False
            Break ForEach  // No need to continue checking; move to the next event. 
        endif
    Next EventRule
    If AddEventToCollectionOfGoodEvents=True
        Add Event to List of Ok Events for this User
    endif
Next EventCurrentlyAcceptingRegistration
Improve this answer
5
  • 2
    Why do you say this isn't a good task for SQL? Or do you mean for MySQL specifically?
    – Jack Douglas
    Commented Feb 24, 2012 at 17:39
  • I mean an SQL query.. you can do this with an SP. Following my Logic. The Table the you need to join to is stored as Data (tablename). (The field is also stored as data). So you would need to Join to all those Tables with a where clause for TableNAme.
    – Morons
    Commented Feb 24, 2012 at 17:42
  • What if a new table is created and you need to add rules for it? You need to modify your SQL. There is just too much overhead here to attempt to do this with one query.
    – Morons
    Commented Feb 24, 2012 at 17:43
  • You can also do this with SQL (non-procedural) in postgres and others - not sure about MySQL. If you do DDL like adding a new table you should expect to need to modify your SQL IMO
    – Jack Douglas
    Commented Feb 24, 2012 at 17:43
  • @Jack, That basically an SP.
    – Morons
    Commented Feb 24, 2012 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.