MySQL and PHP - Open Source at its best!

I've been playing around with MySQL and PHP to create a data enabled website to figure out the activity points for a couple of schools. It's been quite a journey that began with being asked to teach Information Processing about 5 years ago. I had to learn about databases in order to teach IP20. Things started to accelerate last year when I setup a Xibo digital signage server.  Xibo requires a web server and a MySQL database server.  It was through my experience with Xibo that I learned how to use PHPMyAdmin.  I made some initial forays into PHP when I met a teacher from Tisdale who used PHP / MySQL to teach computer science 30.  Then during the summer while visiting my parents, I found a great free database generator - DBQwikSite.  There was still plenty to learn though.  As second semester rolled around, I sat down to figure out how to turn a page of paper data entry into a database.  The key was that I figured it would take two tables.  One table would be to store student data (student number, last name, first name, gender, grade) and a second table to store activity data (student number, activity name, activity points for each grade, activity category).  The primary keys (unique fields) of each table is italicised.  The eureka moment was recognising that the activity data table needed TWO primary keys.  Litwin's paper is a good guide to relational database design. I found out I could host MySQL databases on a free website I had and it went from there.

A few annoyances and design considerations I found:

• Unix servers are case sensitive (for both file names and the MySQL database field names etc).  This was a pain when transitioning from my Windows laptop (localhost) to the webserver.  I was using Windows to develop since I was using DBQwikSite.  I should have just used all lower case!
• Sometimes when copying from Google Docs to PHPMyadmin, the apostrophe wouldn't copy correctly and I would get a MySQL error.  I had to retype the apostrophe in PHPMyadmin to fix that one!
• MySQL doesn't have computed fields like Access because it creates redundant data (a database no no).  Instead, you can make virtual tables with the VIEW command.
• You can actually reuse the DBQwikSite security script with a little modification.  I had made some separate php pages to generate points reports and had to secure those pages.  Very nice - can integrate one's own php pages with the DBQwikSite security.  I couldn't find any feature in DBQwiksite to import custom pages, so this was a great fix!
• I had to use the same MySQL database for both schools on my free website because I'm not allowed more than one MySQL database on my website.  So, the actual database connect would be for the settings on the actual website server, and the DBQwikSite security for users of the database site can be separate.  Also, the single MySQL database would have tables from both schools.
• I should make a master document which has the SQL code for create tables and the create views.  This would save me time to transfer the MySQL database from one machine to the next.  Also, I've been exporting the tables as CSV so that I could re-import the tables on another computer. Exporting CSV files is an easy way to backup a MySQL database (at least for my simple one)!

Now, I'm not big on aesthetics, it should just work.  I know some people don't like Wikispaces, say, because you can't make it look pretty.  I don't really care about that!  Eventually, the Web 2.0 technology will catch up and let you make it look pretty without much effort.  In the meantime, just live with it and try to put out good content.

I really like open source software.  I use LibreOffice, InkScape, VUE, Ubuntu Linux and now PHP and MySQL.  I know there is a dedicated community of volunteers who make this kind of stuff possible as well as some generous companies like Canonical who finance this stuff.  Now, Oracle owns MySQL and I know they're not doing this out of the goodness of their hearts - they can make a buck selling Enterprise versions of MySQL.  Open source volunteers are the antithesis of Apple and Steve Jobs.  I don't have Apple stuff because I don't like what they stand for.

So, in graduate school I spent a lot of time processing seismic data.  Now, I'm processing data again!