Architecture and Data Blog

Thoughts about intersection of data, devops, design and software architecture

ID generation with ORM's Table, Sequence or Identity strategy

In this blog post, I will discuss ID generation techniques using the Object Relation Mapping frameworks such as Hibernate, Toplink, ActiveRecord, Entity Framework. When using hibernate or any other ORM mapping framework. There is a need to generate primary key values for the “id” columns. These values can be generated by using IDENTITY, SEQUENCE or TABLE strategies. Generating custom values for primary keys or other values is a topic for another blog post.


Two factor authentication to authorize credit

Simple flow of credit file usage by Individual Citizens


Synonyms as abstraction layer

In many development shops, developers are not allowed to access the database schema directly, and are not allowed to create tables, indexes, views etc, instead are given access via a different schema that allows SELECT, UPDATE and DELETE access on data. The general reason is to avoid developers creating database objects without


Transactions using Groovy.SQL with Spring annotations and connection pools

When using Groovy with Spring framework, interacting with the database can be done using the Groovy.SQL class which provides a easy to use interface. When using Groovy.SQL, if we have a need to do transactions, we have the .withTransaction method that accepts a closure, to which we can pass in code to execute within the transaction. In our project since we were using spring already, using annotations to define transactions would be a great.

MSSQL JDBC Driver behavior

My latest project involves talking to MS-SQL Server using the JDBC driver and Java. While doing this we setup the database connection and had a simple SQL to get the first_name and last_name for a unique user_id from the application_user table in the database. SELECT first_name,last_name FROM application_user WHERE user_id = ? Given the above SQL, we did not think too much about performance as the user_id was indexed. The java code as below was used to run the SQL.

With so much pain, why are stored procedures used so much

I keep encountering situations where all the business logic for the applications is in stored procedures and the application layer is just calling the stored procedures to get the work done and return the data. There are many problems with this approach some of them are. Writing stored procedure code is fraught with danger as there are no modern IDE’s that support refactoring, provide code smells like “variable not used”, “variable out of scope”.

Schema less databases and its ramifications.

In the No-SQL land schema-less is a power full feature that is advertised a lot, schema-less basically means you don’t have to worry about column names and table names in a traditional sense, if you want to change the column name you just start saving the data using the new column name Lets say you have a document database like mongoDB and you have JSON document as shown below. { "_id":"4bc9157e201f254d204226bf", "FIRST_NAME":"JOHN", "MIDDLE_NAME":"D", "LAST_NAME":"DOE", "CREATED":"2010-10-12" } You have some corresponding code to read the documents from the database and lets say you lots of data in the database in the order of millions of documents.

Effective use of data for better customer experience.

For more than seven years I have been getting offers for credit cards from Airlines and Banks. One particular bank has been sending me these solicitations for more than seven years. That is 12 mailings per year, more than 72 mailings so far, remember these are physical paper mailings not the electronic kind. I don’t like the junk, it hurts the environment and worst of all I think its not good use of the data they have.

Schema design in a document database

We are using MongoDB on our project, since mongo is document store, schema design is somewhat different, when you are using traditional RDBMS data stores, one thinks about tables and rows, while using a document database you have to think about the schema in a some what different way. Lets say, we want to save a customer object, when using a RDBMS we would come up with Customer, Address, Phone, Email.

Ruby OCI 2.0 Array binding

We have been doing some data moving lately using Ruby and Ruby-OCI. We started with Ruby OCI 1.0 and did use prepared statements with bind variables (since we are using oracle database and pulling data from an oracle database and pushing data to an oracle database). Later we found this really cool feature in Ruby-OCI8 2.0 where you can bind a whole array and just make one database trip for many database operations.