Recently while reading “Enterprise Rails” I’ve revisited some of mine assumptions about data modeling. I tend to go from one camp to another, only thing constant is me dissing the wrong camp.

One thing in that book struck a cord with me.

Database as a fortress!

While I’ve never embraced this idea, and have always used RDBMS to protect data integrity (mostly from me at 2AM) the other thing about always using surrogate key made a lot of sense to me. It still does but I am now basically at this:

• Natural keys - if I am really, really certain that they will not change
  • All time favorite zip code or SSN would not be a good candidate in my book because they both did change in my country(yeah I know)
  • Hand generated invoice order number would be a good candidate
• Composite keys
  • Only in weak entities (aka join tables). Main reason is that I protect myself from impossible combinations that you can easily insert into database if you have surrogate key for weak entity. I will explain this later.
  • Those three+ natural composite keys in normal tables - I really, really hate their guts, and don’t care much about them. I still think they are not worthed, and if I want uniqueness I can enforce it anyhow

But wait a minute it is hard work to derail from Rails way! Well not really, it turns out Rails has cool support for both natural and composite keys.

class User < ActiveRecord::Base
  has_many :user_permissions #works as expeced 
end

#natural key, make sure to put :id => false in migration
class Permission < ActiveRecord::Base
  set_primary_key :key
end

#Gemfile
gem 'composite_primary_keys'

#composite key, make sure to put :id => false in migration
class UserPermission < ActiveRecord::Base
  belongs_to :user
  belgons_to :permission, :foreign_key => :permission_key
  set_primary_keys :user_id, :permission_key
end

Now I have a stage set to deliver big win concerning referential integrity enforcement. If I have a table that has history of user permissions I would need permission_id and user_id. This way I would be able to enter combinations that make no sense in regard of user in history and user in user permission. Only way to avoid this is to never have any bugs or enforce integrity at the database level.

class UserPermissionHistory < ActiveRecord::Base
  belongs_to :user
  belongs_to :user_permission,
             :foreign_key => [:user_id, :permission_key]
end

One note, I’ve could have used date/user_id/permission_key as a new composite key. But as I’ve sad I prefer surrogate key in this situation as it gets a bit to advanced for my taste.