Fields To Table Refactor

[plz someone ... refactor this if there's a better page for it. I'm putting it here because Yahoo Groups, naturally, screwed up the code samples... DeleteWhenCooked]

DatabaseRefactor?: Replace Named Columns with a Table of Generic Columns and Metadata

I'm looking for a write-up for this refactor. If there ain't none, then this is the write-up.

Here's the input table:

  create table user
      id <<pk>>
      username         string
      quest            string
      favorite_color   string
      swallow_velocity string

The lower three fields are optional. A very rough explanation of "database normalization" says that optional fields should not be NULL (or, in the case of strings, blank). They should instead reside in a leaf table - a table whose foreign key points back to a main table. That table stores a NULL (or blank) by leaving its row out of the table.

• This is not a universally-accepted practice. Nulls are fine in my opinion, at least compared to the alternatives. There are occasions where something akin to a "sparse matrix" may suggest an AttributeTable, though.

This is the only concept of "DatabaseNormalization" that I understand, but it has not failed me yet. We start by declaring a table in our programming language, outside our database, to name these strings:

 USER_PROFILE_METADATA = [
  [ 1, 'quest' ],
  [ 2, 'favorite color' ],
  [ 3, 'swallow velocity' ],
 ]

(Note that a truly pernicious refactor, that kept _everything_ in the database, would make that into a table, too. That would only improve our design if we intend to allow users to add profile types on the fly. Without that requirement, the design would just be the same as keeping the metadata in our program.)

[Skipping over the "transition period" version], the resulting tables are:

  create table user
      id <<pk>>
      username string

  create table user_profile
      id <<pk>>
      user_id <<fk>>
      metadata_id
      value string

That pattern allows us to replace this View code...

   <li>What is your quest?</li>
   <li><strong><%= @user.quest %></strong></li>

   <li>What is your favorite color?</li>
   <li><strong><%= @user.favorite_color %></strong></li>

   <li>What is your swallow velocity?</li>
   <li><strong><%= @user.swallow_velocity %></strong></li>

...with this:

  <% USER_PROFILE_METADATA.each do |metadata_id, concept| %>
     <li>What is your <%= concept %>?</li>
     <li><strong>
         <%=
           profile = @user.user_profile.find_by_metadata_id(metadata_id)
           profile ? profile.value : ''  # TODO move all this inside the User class
         %>
     </strong></li>
  <% end %>

The View code is now DRY.

Note that we could also have left the table alone, and used USER_PROFILE_METADATA to pull fields out of a single table by their column names. This refactor's value is, among other things, we can add new profile types without further database surgery.

--PhlIp