Handling Advanced Database Features with Rails

Composite keys, primary keys made up of two or more attributes, are best avoided. Not only are they harder to manage than simple primary keys, they are usually more fragile. The motivation for using composite keys is usually based in some inherently unique aspect of the data, which means the composite key will be meaningful (tied to the data) rather than meaningless (tied to the database only). It is usually much more resilient to assign a meaningless primary key used only within the database. That way, data integrity is internal to the database rather than being tied to an external system or process.

As an example, consider a database that tracks U.S. members by their driver's license numbers. A candidate key would be {Issuing state , License number}. One immediate advantage of a meaningless key is that integer values are easier to represent than lists; it is easier to refer to a record as 12345 than as [IL,1234]. This makes foreign keys much simpler, and it simplifies web services and other protocols used for interoperability.

But the most basic problem is that a primary key is usually treated as a unique, stable identifier for a record. A composite key may not actually be unique in practice and may even change. If you were to use the preceding composite key, you should be prepared to answer questions like:

1. What happens when a member moves or has a new license number issued?
   
2. What happens if some inherent characteristic of the key changes? For example, how would you handle it if license numbers were always 9 digits and changed to 10? This is a problem in general with keying off of meaningful data. 
    
3. Are you prepared to have every record with a duplicate or missing key rejected? Or might it be desirable to have the system hold invalid data for a time until it is corrected?

There are some valid situations for using composite keys, though. A good example is in multimaster replication. One big problem in asynchronous multimaster replication is synchronizing primary key sequences. If you insert two records at roughly the same time to two master servers, there must be some mechanism to ensure that the two servers issue different values for the primary keys on each record, lest problems ensue when the records are replicated.

The composite-key solution to the problem of multimaster sequences is to issue each server an ID and use that as part of the key; then each server can maintain its own sequence independently of the others. The two records could have primary keys of
{ServerA, 5} and {ServerB, 5} and there would be no conflict. Note that this is a legitimate use of composite keys, since the keys are meaningless (relative to the data being stored in attributes).

For situations such as this, Dr Nic Williams has made composite keys work with ActiveRecord. The composite_primary_keys gem is available at http://compositekeys.rubyforge.org/.

As an example, consider the multimaster sequence problem discussed previously. We have an Order model that is replicated between two servers using multimaster replication. We must use a composite key to ensure unique primary keys regardless of which server an order is created on. First, we install the gem:

  gem install composite_primary_keys

Then, we have to require this library in our application. From Rails, we can include this statement at the end of our environment.rb:

  require 'composite_primary_keys'

The next step is to call the
set_primary_keys(*keys) method to inform ActiveRecord that we will be using composite keys:

  class Order < ActiveRecord::Base
    set_primary_keys :node_id, :order_id
  end

After settingup the composite key, most ActiveRecord operations take place as usual, with the exception that primary keys are now represented by an array rather than an integer.

  Order.primary_key      # => [:node_id, :order_id]
  Order.primary_key.to_s # => "node_id,order_id"
  Order.find 1, 5        # => #<Order:0x1234567 @attributes={"node_id"=>"1",
                           "order_id"=>"5"}>

Even associations work normally; you only have to specify the foreign key explicitly on both sides of the association. To demonstrate this, we can add a LineItem model that belongs to a corresponding Order.

  class Order < ActiveRecord::Base
    set_primary_keys :node_id, :order_id
    has_many :line_items, :foreign_key => [:order_node_id, :order_id]
  end

  class LineItem < ActiveRecord::Base
    set_primary_keys :node_id, :line_item_id
    belongs_to :order, :foreign_key => [:order_node_id, :order_id]
  end

Note that as in regular associations, the foreign keys are the same on both sides of the association, as there is only one foreign key that defines the relationship (even though, in this case, the foreign key is composed of two attributes). This can be confusing if you don't consider the way the relationship is represented in the schema, because the foreign_key option defined in Order's has_many :line_items statement actually refers to attributes of LineItem.                                             

As a final touch, we can set things up so that we don't have to worry about the keys at all in code. Remember that the original reason for using composite keys was to allow us to use independent sequences on each database server. First, we create those sequences in SQL when creating the tables. The way we set this up is DBMS-specific; the PostgreSQL syntax would be:

  CREATE SEQUENCE orders_order_id_seq;
  CREATE TABLE orders(
    node_id integer not null,
    order_id integer not null default nextval('orders_order_id_seq'),
    (other attributes)
    PRIMARY KEY (node_id, order_id)
  );

  CREATE SEQUENCE  
line_items_line_item_id_seq;
  CREATE TABLE line_items(
    node_id integer not null,
    line_item_id integer not null default nextval('line_items_line_item_id_seq'),

    -- FK to orders
    order_node_id integer not null,
    order_id integer not null,

    (other attributes)
    PRIMARY KEY (node_id, line_item_id)
  );

When we execute this DDL on all database nodes and enable replication between them, each node has its own sequence independent of the others. Now we just have to make sure that each node uses its own node ID. We could either do this in the database with column defaults (if we can use different DDL for each node) or in the application with a before_create callback (if each application accesses only one node).

Please check back tomorrow for the continuation of this article.