This week started out slowly because it took a bit of getting used to the work routine again after the past long weekends…
In order to get back on track and exercise some SQL 2005 compliant stored procedure coding, I decided to collapse the whole business logic into the database
First I had to explain my database structure to my colleagues to make sure they know what I’m doing and to find out if my design holds up to its promise of being generic and flexible enough for any data source and structure.
Basically I am implementing a generic statement language/toolkit to allow users to pick any given data as the source and combine it with a function or function set. So, yeah, a SQL based and web accessed version of a custom tailored excel/access-style application focused on the data analysis part while automating the data import part across 3 companies.
In order to do this i am using dynamically generated SQL statements written in SQL using stored procedures. That’s because almost every database request I make uses lots of cross table references.
Of course I do not know everything be heart so I had to relearn some of the stuff specific to MySQL from these excellent tutorials which i really recommend to everyone interested in a bit more sophisticated database usage models:
- a cross table query beginners guide
- combine this now with some multi-level aggregation…
- and use this in stored procedures (it’s a 4 part series)
- a very nice and short practical example
So in the end just what you would normally do with lots of database requests from your backend all done directly in the database which really pays off with big amounts of data.
Another thing I can proudly report is that I got my free tickets that allow me to travel through Northern Ireland for free with all means of public transportation. So expect more travel reports in the future đ