Refactor Steps
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.
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;
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;
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 abelongs_to
relationship with the new model class.
Figure 6-8. A table not in third normal form
Figure 6-9. The ...
Get Enterprise Rails now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.