Enterprise Rails

Refactor Steps

1. Find a table in which a column takes on one of a set of values, but those values are not foreign key references into another table, as in Figure 6-8.

2. Create a new table in which the rows represent the set of values from 1 and any other columns that were dependent on this value, rather than the original table’s primary key (Figure 6-9).

   We can create the table shown earlier with the following SQL statement (note that it is up to you to account for any data inconsistencies caused by improper normalization):

   insert into colors(name, hex)
   select distinct color, hex from my_table;

3. Create a column in the original table for the foreign key reference, then populate it. After doing so, delete the original columns (Figure 6-10).

   alter table my_table add column color_id integer;
   update my_table
      set color_id = (
        select id
          from colors
         where m.color = c.name
           and m.hex = c.hex
      );
   alter table my_table drop column color;
   alter table my_table drop column hex;
   alter table my_table add constraint my_table_color_id_fkey
     (color_id) references colors(id);
   alter table my_table alter color_set not null;

4. Create a model class for the new table. Add association references appropriately. The new model will have a has_many association to the original model class, and the original class will have a belongs_to relationship with the new model class.

Figure 6-8. A table not in third normal form

Figure 6-9. The ...