Tuesday, November 28, 2006

Ruby on Rails Anti-Pattern?: Primary Key Visibility, Using in Code

Using an autoincrement integer field as the primary key / object id lets the Rails table relationship magic flow, and is a common design pattern in other O/R frameworks. Be careful about using this value for other purposes, however. It is typical to consider using the ID for such things as:
  • As a customer number, part number, invoice number, etc. visible to users or printed on documents.
  • Explicitly referenced in your source code to drive business logic.
Why is exposing or referencing the ID a potential problem? It may bite you in several ways:
  • If using ID's to drive business logic, your data is no longer easily portable between databases. This is particularly a problem when workinging on a project with multiple developers each running on their own local development database. It is possible to keep configuration record id's related to business logic in sync., but takes some effort. Throw in test databases, test fixtures, production systems, demo systems, etc. and you are signed up for even more synchronization fun.
  • There are surprising and annoying non-technical reasons that may cause the need to change formatting and layout of customer numbers, invoice numbers, part numbers, etc. It is very unpleasant to have those as part of your key structure if you have to restructure them. (A particularly ugly example: systems using Social Security Number (SSN) as a primary key, foreign key, etc. are a nightmare to rework when eliminating/hiding/encrypting SSN for privacy reasons.)
  • Sometime during the lifecycle of your application, it is possible that you will move your production data to a different database instance or platform. While you do have to maintain foreign key integrity among ID's while performing such a task, you will at least not have to worry about also synchronization with the outside world to maintain the integrity of the customer numbers, invoice numbers, or part numbers.
What to do instead? Consider incurring the wrath of the database normalization gods:
  • Use a separate, non-primary key field for identifiers visible by your users. This is a bit of work as you have to roll another autoincrement field, driven by a separate table, but is worth considering. Having such a field decouples your database structure (primary and foreign keys, table relationsihps) from the whims of how users use an identifier as a purchase order number, invoice number, customer number, part number, etc.
  • Use a mapping field of some sort for objects that must be explicitly identified in code to drive business rules or other behavior. This is easy to do, but does incur some run-time overhead as you now reference my_object.some_other_object.map_field instead of my_object.some_other_object_id in your code.
  • In the case of configurable selections against which you may have to code logic, don't use a table (and accompanying model) just to have an database driven drop down selection box. If there is no logic or other data associated with the selections, simply store the actual selection value in your record, thereby eliminating the object ID issue altogether.
Related: See c2.com for a great catalog of anti-patterns.


Anonymous said...

I don't think this anti-pattern is exclusive to RoR. If you use the primary key id in ORM solutions in other languages like Java, C#, or Python, you're going to be in trouble later on too.

Anonymous said...

Huh? Why would you be in trouble later on? The primary key approach used in ORM impls in Java, etc (like Hibernate) use a surrogate key approach, which is what the article you read above advocates and is really the best approach to prevent problems downstream. Please, think before you type.

rbaldwin said...

Regarding the above comment: To put it more succinctly - once you have a surrogate key, do not use it for other purposes. The reasons why were articulated in the article.

casey said...

Primary keys can be useful in code, when they are used to test object identity. Beyond that, I completely agree with you.