Join vs. Where for speed - help me explain why I do it!
Posted on 2013-06-03
I'm in a bit of a bind. I've been designing databases for quite a while and I've always tended to use joins to restrict data rather than having a bunch of indexes in the same table.
Currently, one of my clients hired their own "consultant", who's been nagging me about every design decision and it's driving me crazy trying to explain "why" I do things.
Here is the most recent example:
Our inventory system does a lot of crazy analysis on stock, but some products are just unimportant. For example, You don't need to do a lot of analysis on office supplies when you're tracking the inventory of o a factory that makes cameras, even if they're in the same table.
I have a table where I have product_features, which simply has a product_id and a feature code. When I save the product, I insert a record in the product features table that says, product_id, "trackstock". Then, when I want to pull the inventory for an analysis report, I start with a view like this:
select p.* from products p inner join product_features on product_id = 123 and feature_code='trackstock'
however, the obvious alternative would be to add a bit column in the products table for track_stock, so you could just do this:
select p.* from products p where track_stock=1
In regards to load, this table is going to have 5 million + records in it. I'm mostly concerned that we're going to end up with a table that's got 100 columns
In this case, I'd say the guy has a point, and I told him to go ahead and make yet another new column, and another new index on that table, but honestly, am I crazy? Is my constant use of joins to restrict data just a bizarre habit, or is there some justification to it??
Another example of my use that he disagrees with is for security. I have procedures where you send a request for orders, but you only want to get the orders from the offices where that user has access, so instead of doing:
select * from orders where office_id in (select office_id from office_security where user_id=123) - and a bunch of other ifs if the guy is an admin...
I actually use a table function so I can do this:
select o.* from orders o inner join security(@user_id) sec on sec.office_id = o.office_id
I dunno. Maybe I'm crazy. Maybe I'm sticking to my guns so hard because I'm not used to someone inexperienced, yet tells everyone he's a genius, questioning my every decision.
anyone with recommendations for my sanity?