Mostly I’ve done work in Oracle but I’ve recently had more exposure to PostgreSQL. I’ve some some work in Microsoft SQL Server, MySQL and Sybase.
As noted in my Java experience, the tools required database to persist data. The first rate tool uses Oracle and second uses PostgreSQL. I wrote both databases used by the tool. Tables are linked together using referential constraint. Both tools have auto-generating keys (GUID/UUID).
In the Oracle database, I’ve created several stored procedures used for ETL. The database has several named-pair tables consisting of a key(refers back to a master table), a field name and a value. A master record may have several hundred of these named-pair records. Of course, the simplest method to get these values is to create several joins, one per field. I came up with an alternative method (I call it turn and squish) where I pull all the “field records” in the base query and with subsequent wrapper SQL, turn the rows into columns. Within the last year, I’ve learned about the ‘pivot’ construct in Oracle that does a similar function.
In PostgreSQL, I’ve, written stored procedures, although, this construct is different than Oracle. More like a function, the query is constructed to return a single value, although a set of data can be returned. I’ve created stored procedures for our new policy administration system to return rates. Each rate has specific criteria, so the stored procedure is design to accept only the criteria needed for that rate.
I have spent time doing performance review and enhancements of existing queries and stored procedures. Adding indexes, restructuring the query or using intermediary table are just some of the techniques used. I have used Ask Tom quite a bit to help.