Database

In the previous article I showed how a model can be disabled. One of the things that you probably noticed is that I’m using a timestamp (DateTime) field instead of a Boolean field. This is a trick I learn years ago after working on a large database (>2B rows) for an Enterprise client. It has been useful in so many ways that I put it into every database design I do, yet I’ve never written about it before and only talked to developers in person about the purpose of the design. ...

Here is a quick tip on how to install the pg ruby gem on OSX if you only have Postgres.app installed. First, if you attempt to install the pg gem it will fail with: $ gem install pg Fetching: pg-0.17.1.gem (100%) Building native extensions. This could take a while... ERROR: Error installing pg: ERROR: Failed to build gem native extension. /Users/me/.rbenv/versions/2.1.5/bin/ruby extconf.rb checking for pg_config... no No pg_config... trying anyway. If building fails, please try again with --with-pg-config=/path/to/pg_config checking for libpq-fe.h... no Can't find the 'libpq-fe.h header *** extconf.rb failed *** If you search for solutions to this issue you will undoubtedly be told to install the postgresql package from Homebrew. That’s nice, but you already have the Postgres.app and you don’t want to maintain 2 versions of the same application on your machine (it is also not isolated, and can cause port conflicts). ...

One of the things that I dislike seeing in an application’s database architecture is the use of booleans. They do not convey enough information to any party to be meaningful, other than to ask a question that expects a yes/no answer. Often, questions such as those as a follow-up question that the field cannot answer. For example, in many databases I see the following fields on a User model: User ==== id primary key username the user's username credential password_encrypted authenticates the username ... other fields ... enabled true if the user is active, false otherwise When a user has been banned for doing something inappropriate, an application simply sends this query: ...

Not enough people give careful thought about ensuring uniqueness across systems when they design a solution. This goes doubly so for off-the-shelf (OTS) software packages. And if the design isn’t well thought out, it is going to be difficult to ensure uniqueness when doing any data migrations. Granted, data migrations are an art more than they are a science. There are always edge cases and invariably something goes wrong that nobody thought about. This is why testing a data migration multiple times should be built into any project plan (and given an extremely large buffer in the budget). ...