If only organizing your home was like SQL correlated subqueries
I remember the first time I saw correlated subqueries in SQL, it seemed kind of magic, until I found out how they worked. When I saw this example in "Teach Yourself SQL in 21 days"...
SELECT O.PARTNUM, SUM(O.QUANTITY*P.PRICE), COUNT(PARTNUM)
FROM ORDERS O, PART P
WHERE P.PARTNUM = O.PARTNUM
GROUP BY O.PARTNUM
HAVING SUM(O.QUANTITY*P.PRICE) >
(SELECT AVG(O1.QUANTITY*P1.PRICE)
FROM PART P1, ORDERS O1
WHERE P1.PARTNUM = O1.PARTNUM
AND P1.PARTNUM = O.PARTNUM)
(the chapter it's taken from can be found here. The whole book is online.)
...I wondered how is it possible to perform the inner query when we don't know what O.PARTNUM is, when in fact O.PARTNUM is what we are trying to select. In some part of my mind an image popped up where the inner and outer query were like cogwheels spinning each independently of the other, and their teeth were symbolic of O.PARTNUM, and then with a snap they interlocked, based on a magic guess of what O.PARTNUM was. Or something like that. I know this metaphor doesn't make much sense. :-) It did not form in my head fully fleshed out, rather it was vague, fluid, incomplete and shapeless.
And of course it was wrong, because as I found out later, the correlated subquery is invoked for each row, or in this case, each group, of the main query. So there isn't really any magic.
For some reason I remembered it when contemplating how to organize the kitchen, and apartment in general, after the move.
You can, to some extent, organize one area at a time, but only to a limited extent. While some things definitely belong in, say, either bedroom closet or kitchen pantry, there are things that could easily find a place in any of several different closets. So, to find optimal space for every item -- by optimal I mostly mean, you should be able to find it easily, and you should not forget that you had it and buy another item of the same kind -- you can start by thinking of each storage space separately, and envision possible permutations of items in each closet and cabinet separately, but there will be dependencies. Items in different spaces will correlate will each other in interesting ways. Eventually you will have to visualize all the storage space in your home at once, with the number of possible arrangements increased many times by various space-doubling and -tripling equipment you can use to store stuff. Stacking shelves, crates, dividers, drawer organizers...
So, one of those figurative mental gear wheels is spinning the permutations of items in, say, a kitchen pantry; and another, the permutations of the hallway closet, and there are items that can go into either, and they create interesting dependencies, like that O.PARTNUM in the correlated subquery. I know it is a very lame metaphor when I try to explain it, but it came to my mind naturally, so it may reflect the reality to some degree... my inner reality, at least. :-)
If only organizing could be performed the way correlated subqueries are evaluated. If O.PARTNUM referred to an actual household item, I wish I could find a tentative location for it, and then keep it fixed as I arrange all the other items; then compute the "optimalness" of this arrangement, then find a new tentative location for O.PARTNUM and rearrange the rest of the items; and so on until I find an optimal arrangement for all items. Alas, if I did that, I would spend the whole life doing nothing but organizing. If only they were bits and bytes, not physical objects to manipulate.
Until that day comes, I will have to rely on mental magic that SQL actually does not have.
SELECT O.PARTNUM, SUM(O.QUANTITY*P.PRICE), COUNT(PARTNUM)
FROM ORDERS O, PART P
WHERE P.PARTNUM = O.PARTNUM
GROUP BY O.PARTNUM
HAVING SUM(O.QUANTITY*P.PRICE) >
(SELECT AVG(O1.QUANTITY*P1.PRICE)
FROM PART P1, ORDERS O1
WHERE P1.PARTNUM = O1.PARTNUM
AND P1.PARTNUM = O.PARTNUM)
(the chapter it's taken from can be found here. The whole book is online.)
...I wondered how is it possible to perform the inner query when we don't know what O.PARTNUM is, when in fact O.PARTNUM is what we are trying to select. In some part of my mind an image popped up where the inner and outer query were like cogwheels spinning each independently of the other, and their teeth were symbolic of O.PARTNUM, and then with a snap they interlocked, based on a magic guess of what O.PARTNUM was. Or something like that. I know this metaphor doesn't make much sense. :-) It did not form in my head fully fleshed out, rather it was vague, fluid, incomplete and shapeless.
And of course it was wrong, because as I found out later, the correlated subquery is invoked for each row, or in this case, each group, of the main query. So there isn't really any magic.
For some reason I remembered it when contemplating how to organize the kitchen, and apartment in general, after the move.
You can, to some extent, organize one area at a time, but only to a limited extent. While some things definitely belong in, say, either bedroom closet or kitchen pantry, there are things that could easily find a place in any of several different closets. So, to find optimal space for every item -- by optimal I mostly mean, you should be able to find it easily, and you should not forget that you had it and buy another item of the same kind -- you can start by thinking of each storage space separately, and envision possible permutations of items in each closet and cabinet separately, but there will be dependencies. Items in different spaces will correlate will each other in interesting ways. Eventually you will have to visualize all the storage space in your home at once, with the number of possible arrangements increased many times by various space-doubling and -tripling equipment you can use to store stuff. Stacking shelves, crates, dividers, drawer organizers...
So, one of those figurative mental gear wheels is spinning the permutations of items in, say, a kitchen pantry; and another, the permutations of the hallway closet, and there are items that can go into either, and they create interesting dependencies, like that O.PARTNUM in the correlated subquery. I know it is a very lame metaphor when I try to explain it, but it came to my mind naturally, so it may reflect the reality to some degree... my inner reality, at least. :-)
If only organizing could be performed the way correlated subqueries are evaluated. If O.PARTNUM referred to an actual household item, I wish I could find a tentative location for it, and then keep it fixed as I arrange all the other items; then compute the "optimalness" of this arrangement, then find a new tentative location for O.PARTNUM and rearrange the rest of the items; and so on until I find an optimal arrangement for all items. Alas, if I did that, I would spend the whole life doing nothing but organizing. If only they were bits and bytes, not physical objects to manipulate.
Until that day comes, I will have to rely on mental magic that SQL actually does not have.