I’m working on an array structure that will be used as the data source for a report template in a web app.
The data comes from relatively complex SQL queries that return one or many rows as one dimensional associative arrays. In the case of many, they are turned into two dimensional indexed array.
The data is complex and in some cases there is a lot of it. To save trips to the database (which are extremely expensive in this scenario) I’m attempting to get all of the basic arrays (1 and 2 dimension raw database data) and put them, conditionally, into a single, five level deep array. Organizing the data in PHP seems like a better idea than by using where statements in the SQL.
Array Structure
Array of years(
year => array of types(
types => array of information(
total => value,
table => array of data(
index => db array
)
)
)
)
My first question is, is this a bad idea. Are arrays like this appropriate for this situation?
If this would work, how should I go about populating it? My initial thought was shallow to deep, but the more I work on this, the more I realize that it’d be very difficult to abstract out the conditionals that determine where each item goes in the array. So it seems that starting from the most deeply nested data may be the approach I should take.
If this is array abuse, what alternatives exist?
Edit
I went ahead with this approach, learning and changing things as I went. But I ended up with a similar end array. What I learned in regards to the shallow to deep conundrum was this.
I went into the process thinking of the outer arrays as the largest of a set of cups, containing multitudes of smaller cups which in turn contained another multitude of smaller cup until the last set which in my mind was water (this being the indexed array of database values). Arrays are not cups…
The idea fell apart when I started legitimately hand drawing out the array structures and found that the base of these arrays, the water filled tiny cups I had envisioned, represented a heck of a lot more data than those big cups. This lead me to see these deeply nested arrays as towers, with the indexed data representing a base of sorts, tapering up to the tip, which was the containing array.
From there, I figured out that the best way for me to process the data was to gather the bottom data, plug it into it’s next layer and then go up the tower plugging each layer into it’s next layer.
I’m not sure if I’m right, but I do feel like I understand arrays more now than I did before.
And I’m definitely still not sure if this is the best solution.
1
I’m not sure I understand the problem you’re trying to solve. Can you give us a sample SQL query?
- Are you joining five different tables using 4+ relations in order to render a single query? If so I’d recommend trying an ORM. Any good MVC solution for your language (e.g. CodeIgniter) should have something for you.
- Are you trying to load the full contents of five tables into memory (either at the PHP server or on the client) so that you can join them at will once the user selects something that describes the necessary join? If so (and assuming there’s not all that much data) then you might want to try a Javascript-based frontend ORM, maybe using something as simple as Backbone.js.
I must admit that I’m not entirely certain either of the above suggestions is appropriate for your problem as described. I’d love a bit more detail.
1
How do you size your inner arrays? I guess you don’t know in advance the size for each of them.
I see three possible solutions:
-
If the database is not changed often, but rather read, you can index your tables (internally or with Lucene). It will bring more performance.
-
Use a B-Tree, because it’s also friendly with respect to hard disk “swapping” (I guess you don’t want to keep everything in memory).
-
a combination of 1 and 2.
Full-disclosure: I’m coming from more of a JSON background but designing with object literals and arrays is pretty much the same problem.
I would design with the following goals:
- Always make a data structure as shallow as possible until you start needlessly duplicating values all over the place (i.e. over-flattening to the point where you’re needlessly duplicating entire sets for different keys/indeces). People tend to nest things for nesting’s sake alone or because it seems like there’s too many items in a set. All that matters is whether everything belongs in a given category. Note: with smaller data sets, sometimes flattening everything for simplicity is a great idea, but this doesn’t sound like one of those scenarios.
- If the ordering of elements isn’t critical and a set has a reliable unique value that’s useful as a key, convert it from indexed to associative with that unique value as the key. i.e. Don’t loop something and check for the right value if you can just reference directly by name. This can drastically reduce the complexity of your access functionality.
- On the flipside prefer indexed when multiple items with no reliable unique value are possible or the order you’re going to loop through those items in is important (maybe that 2nd point is less critical in PHP).
- Don’t conditionally format. That thing with 1D when it’s only one item or 2D-indexed when it’s many sounds like a recipe for pointless logic to me. Looping over a one-element array half or even 75% of the time is much less crufty/brittle.