We had a rather large amount of data to import into a shoppingcart. We wanted to try on both Oscommerce, and the wordpress shoppingcart plugin. Manually entering this huge amount of data already available in excel spreadsheets was out of question. We made this tool to translate the excel data into both oscommerce database and wordpress database.
Transferring data across various databases and sources/destinations is our strongpoint. However, the oscommerce data structure posed a rather unique problem. Extended and stretched by countless programmers to meet varying needs, the data structure contained repeated data, excessive tables, no indexes, and was in general highly unnormalized.
Understanding the oscommerce data structure was our first step, and an ER diagram proves highly helpful in this matter. We started creating an ER diagram, found the data structure to be too large and complex to do it quickly, and turned to google. Found one pretty quickly. We opened the pdf, and sat back to admire the criss-cross of relationships and the multiplicity of entities that represented the oscommerce data structure.
Still, a client's request is a client's request, and we had to import their data into both oscommerce and wordpress shoppingcart to make a comparison. The next step was creating a object data structure in java to store the excel data, and act as a facade to the two database writer classes which would write to oscommerce and wordpress shoppingcart.
Our initial attempt at creating the import utility lead to repetation of options, since we made the naive mistake of importing option values separately for each option set. After a count of the number of option values from the database, we quickly changed our approach, and eliminated duplicate option sets. Now, a new option set was added only if the exact same set of values was not already in database. This seemed to create a reasonable number of options in the database - 13559 option values for 894 options, for 149 products.
The shoppingcart seemed to run fine with this dataset. However, when we tried to administer the options from oscommerce admin pages, we ran into a different kind of problem - unmanagable page style. To solve this, we had to create a new admin page to manage options.