Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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 Feb 24 '12 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 Feb 24 '12 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 Feb 24 '12 at 19:04
add comment

migrated from programmers.stackexchange.com Feb 24 '12 at 17:17

This question came from our site for professional programmers interested in conceptual questions about software development.

2 Answers

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.

share|improve this answer
add comment

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
share|improve this answer
2  
Why do you say this isn't a good task for SQL? Or do you mean for MySQL specifically? –  Jack Douglas Feb 24 '12 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 Feb 24 '12 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 Feb 24 '12 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 Feb 24 '12 at 17:43
    
@Jack, That basically an SP. –  Morons Feb 24 '12 at 17:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.