Tuesday, August 14, 2012

A *REAL* Simple Employee Table for PostgreSQL Retirement Planning

There was a time ~ 15 to 20 years ago, when Retirement Plan Recordkeepers were saddled with less than friendly reporting tools.  We would collect data from these Recordkeepers (Quarterly) and design small, unique sets of tables for each plan, along with the output design (print) that would be understandable for each employee, to include Future Values, charts and other communication materials. At one point, we were running 500 Retirement Plans and ~100,000 statements, each with a different print style and most, with very different table designs.

My point?

The Table below is extremely simple and does not scratch the surface of a Recordkeeping system - but - it is just enough to run and test our initial PostgreSQL functions.  Any HRIS employee could do the same, with ease.  You wouldn't need a recordkeeper and could run it whenever you chose to communicate to either one - or many employees.  One day of work by one person in an HR Department can change the future of many.  'Too busy', is thin.

Younicycle has a built-in Table Editor that records mouse clicks, selections & entered text and then creates the SQL.  Anyone (yes, really) can create an SQL Table.  It may not make a lick of sense, but it can be done without knowing a bit of SQL.

The image below shows the Columns, default (automatic) values and NULL constraints.  Once again, there still are no values in the Table.  Also notice that I'm using double precision (instead of numeric) for many of the datatypes.  There are practical reasons for this that I'll hit on next week.



So, let's add some values.  See below and note that we do not need to enter values for start_date or annual_growth_rate as we have set Defaults that 'automatically enter' the stated values.



Pretty simple stuff.  If you are an Employee Benefits Manager, you certainly get much greater detail from your Recordkeeper and chances are high that it will include all of the above + more.

Next post, I'll show you how to write a query that will use those pgSQL Functions and the data.

* Note - obviously, you won't want to data enter values for 10 .... or 10,000 employees.  We have a data import dialog for that, which I'll show in a future post.

Thanks for reading.

No comments:

Post a Comment