For a stationary website with a very large number of products and product options, we faced the problem of an unmanagable options admin page in oscommerce. We decided to roll our own admin page for these options.
The client got annoyed trying to manage countless options (about 30,000 options in 10,000 products) in the admin page. Our first instinct was to try some existing contribution - perhaps the ajax admin plugin.
No good. The admin page was unwieldy as ever. We decided to sit on our desks and make a fresh design for the page. Made a mockup on paper, followed by an HTML mockup. This was then handed over to our dev team.
Then we faced another problem - the oscommerce data structure. First off, its a maze. Thankfully, we had already found and downloaded an ER (Entity Relationship) diagram for the oscommerce database for our recently developed bulk-import tool in java to bulk-import data from Excel directly into oscommerce. Even with this diagram available, the oscommerce data structure is not only a maze, but highly unoptimized too, as sql and normalization rules go. The table structure, generalized and extended to accomodate the requirements of multiple programmers, becomes a sluggish bottleneck when faced by a large volume of data.
As we continued developement of our admin page, some queries required to collate data into meaningful sequences started taking enough time for us to plant and grow coffee, pick the beans, boil water, make coffee and then drink it too, all the while the page is loading. We realized (yet again) the importance of a normalized and quality data structure in large data-volume systems, and added another of those sticky notes to our mind to re-evaluate and ensure a normalized data structure in all of our offerings.
To speed up our page, we created numerous indexes, based on our query requirements. This had the magic effect, and the next time our programmer reloaded the page and started getting up to grow his coffee beans, the page was there already.
The new page
Finally, after a couple of iterations between the user friendliness experts and UI designers and programmer, the all new page was ready; this is what it looks like...