Wednesday, June 01, 2005
Today a colleague asked me for my take on a problem he was working on. He was trying to keep track of allocations for a given network of facilities. So for a particular facility, say an oil well, the allocation result would store the path that the oil took through various facilities. A more concrete example: Oil Sales Allocation, from Battery B2 to Well W1: W1 <- B1 <- B2. His approach at the database level was to store each segment of the path is a database record and to connect the top record to an allocation result record. The problem he was running into was how to match up a path to an allocation result. So suppose I know a given allocation was supposed to follow the path W1 <- B1 <- B2, how to I find the matching allocation in a reasonably efficient way. I suggested that he try something simpler to start with: Just store the allocation path as a string inside of an allocation record. That way, it's easy to match up the expected path with the actual path. If something more normalized becomes required at the database level, one can always use some PL/SQL (we're using Oracle) to create the needed string from a more complex database structure. He walked away with some enthusiasm for the idea. Now, we'll see if this particular idea actually works out in practice, but the point is that if you're running into a tough problem, the simplest approach might be to think of ways to avoid having to solve it altogether. It doesn't always work, but I have found this sort of approach to be a useful way of simplifying problems and therefore the corresponding design.