Kindergarten Lottery Technical Information
How did the data get in the database?
Warning: this is not the "best way" to do it - I am working on that. *grin*
I took the spreadsheet and first imported it into a Microsoft Access database, which was
relatively painless. After a bit of playing around, I was able to get the spreadsheet
loaded into a Microsoft SQL Server (hereafter MSSQL). I generated some charts using this
setup, but was unsatisfied and wanted something more dynamic; I really wanted to use
a web front-end to support dynamic queries so folks could ask whatever they want and make
the data as interactive as possible. That led me to loading the data into a MySQL database
(hereafter mysql), which makes it really easy for me to write php pages to go against it.
And Voila, here we are.
So, keep in mind I preserved the data as much as possible, including cryptic column names.
All of the worksheets from the original spreadsheet are included, and some of them
(ie, "SEAT CONTROL") were already formatted - they were loaded "as is". Some data has
Microsoft-specific characters (ie, "smart-quotes") and these were translated into "unknown"
characters. I figured they did not make much of a difference, but perhaps that can be
something to look into in the future. Also, I did not like how the numbers, by default,
were translated into "doubles", meaning they are floating numbers with excessive precision
(should only be integers).
Update 25May2010
I decided against adhoc sql access. There are several issues involved with offering free query
access. Most of all, this entire site is hosted as a privilege and free of charge, and I dearly
do not want to abuse that. I am not only concerned about sql-injection attacks (humorous example),
but I also do not want to be in the business of helping folks tune their queries, or teach them
how to write SQL. I mean, actually, I do very much (I teach a class at Parkland), but I do not
want to get wrapped up in that for this particular project.
To Do
- Ask/Request/Convince/Cajole Dr. Alves to send future data in a more readily-consumable
format (ie, standards, legend/key, etc).
- Work with Unit 4 so they host the database and perhaps these php pages as well.
Develop methods to support multiple-year data. For instance, I have 2009 data - the
smart thing to do is to introduce a timestamp, perhaps in the table names. A good
data model will incorporate segmented data in a partitioned table.
Would love to offer ad hoc SQL without compromising the host - still chewing on that one.
Would also love to implement java tables - the ability to sort the table output based on
column data. I have not done this yet because it depends on who is hosting, and what kind
of java libraries they have installed. There are some really cool widgets out
there (1,
2,
3,
4).
As of June 7, 2010