by Community Contributor on April 25, 2007 01:39pm
This morning, a guest blog from Gerardo Narvaja, Senior Sales Engineer from the MySQL User Conference...... In Bryan’s article he used the metaphor calling the coopetition between Microsoft and MySQL the “beautiful game”, or like the Brazilians like to call it: “jogo bonito”. I will try to exemplify it scripting what could be a real world scenario. I will be making a quick demo based on this article during the Primetime ODBC: Constructing ODBC Applications and the ODBC 5.0 Roadmap talk at the MySQL Users Conference on Thursday at 11:50 AM.
Situation: How many times did you wonder if some neat and big spreadsheet could be imported quickly into a DB in order to be able to do queries and data manipulation beyond Excel’s capabilities? How many times did you prototype in Excel what would be a few tables prototyping the solution to a given problem and now need to create the DB to start developing your application?
Problem: Just to illustrate this problem I chose to use the data generated by the Nike+ iPod accessory. The data is stored in the iPod file system XML format and it can be imported into Excel with relative ease. In order to keep the problem simple, I only extract a portion of the data and didn’t translate the fields to more adequate data types (see schema at the end of the article). For the sake of simplicity I will not illustrate the process to import this data, there are plenty of examples in the web.
Solution: Following is a series of steps to follow combining tools from Microsoft and MySQL in a Windows environment to quickly convert a simple Excel table into an actual database. This article assumes that the proper DSN to access MySQL is already configured; otherwise check the article in the Port25 site before going forward.
The whole process shouldn’t take more than 30 minutes depending on the complexity of the initial data. The next steps could be normalize the database, extend the DB schema, etc. Using Access it is possible to quickly develop a prototype a proof of concept. The MySQL server can also be accessed using the Connector/.NET allowing to create the final solution using Visual Studio .NET.
Conclusion: Most of the time, when we face a problem for which we are looking to device an IT solution, we start with a group of data representing such problem. By using Microsoft’s tools as described above it is easy to represent this data in a database and quickly create the necessary tools to work with it. Using the MySQL Server and the MySQL Tools it is possible to create a client/server backend that will carry the initial database prototype from a quick proof of concept into the final solution. All these operations are facilitated by the Windows environment.
- Gerardo Narvaja, MySQL Community Manager