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.

Typical wep app situation: you have logged in users querying for resources exposed through an API. As a hypothetical example, company administrators quering for their employees' time sheets:

company <- employees <- time_sheets
company <- admin

Say I wanted to get all the time sheets that I'm authorized for/I have access to as an admin of that company.

Without any optimizations, in the above I'd have to join admin with company with employees with time_sheets to determine all the time_sheets I can look at. You can see how for a wider schema the joins could add up very fast. I realize that in modern databases joins are ludicrously fast, but I'm guessing this is still a case you don't want to completely ignore.

Is there a good solution to this? One could potentially have a separate admin to time_sheets many to many join table just to speed-up those queries, but then maintaining it might be tricky.

Any suggestions?

share|improve this question
    
Like most things it's all about trade-offs. You can denormalize, maintain materialized views, etc, but all that has its own costs. It really depends on workload, query patterns, response time priorities, maintenance burden you're willing to bear, etc. –  Craig Ringer Jun 1 '13 at 16:36
    
Craig, would you be able to recommend something I could reference to make that decision? Ideally something specific to this ACL-like situation. Nothing too overly sophisticated, I would not be able to appreciate that just yet, but something that would get me at least 80% there. –  glitch Jun 1 '13 at 17:47
1  
Normally the questions like "all of ..." lead to a large number of results, and no one looks at a giant list of results. Make sure you understand and analyze the use cases of the users, provide good restrictions like for date or for city. Put the right indices for these restrictions and those joins should be no problem. In case of select time problems you can preselect parts of the chain (and cache them) - if a user logs in, his position as admin for a company won't change in a few minutes. So you have a list of companies he is admin for. –  flaschenpost Jun 1 '13 at 19:40
add comment

1 Answer

up vote 1 down vote accepted

Like most things it's all about trade-offs. You can denormalize, maintain materialized views, etc, but all that has its own costs. It really depends on workload, query patterns, response time priorities, maintenance burden you're willing to bear, etc.

In general I'd recommend starting with the properly normalized design and join chain, but doing it via views for common queries. This allows you to later replace the view with a trigger-maintained materialized view, to selectively denormalize the schema, etc, without messing with the app's queries.

Don't try to fix it until you know it's actually a problem.

share|improve this answer
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.