data modeling - Moving table columns to new table and referencing as foreign key in PostgreSQL -

suppose have db table fields "id", "category", "subcategory", "brand", "name", "description", etc. what's way of creating separate tables (eg.) "category", "subcategory" , "brand" , corresponding columns , rows in original table becoming foreign key references?

to outline operations envolved: - unique values in each column of original table should become foreign keys; - create tables - create foreign key reference columns in original table (or copy)

in case postgresql db accessed via sequel in ruby app, available interfaces command line, sequel, pgadmin, or (...)

the question: how this?

        -- test data create table animals         ( id serial not null primary key         , name varchar         , category varchar         , subcategory varchar         ); insert animals(name, category, subcategory) values  ( 'chimpanzee' , 'mammals', 'apes' ) ,( 'urang utang' , 'mammals', 'apes' ) ,( 'homo sapiens' , 'mammals', 'apes' ) ,( 'mouse' , 'mammals', 'rodents' ) ,( 'rat' , 'mammals', 'rodents' )         ;          -- [empty] table contain "squeezed out" domain create table categories         ( id serial not null primary key         , category varchar         , subcategory varchar         , unique (category,subcategory)         );          -- original table needs "link" new table alter table animals         add column category_id integer -- not null         references categories(id)         ;         -- fk constraints helped lot supportive index. create index animals_categories_fk on animals (category_id);          -- chained query to:         -- * populate domain table         -- * initialize fk column in original table ins (         insert categories(category, subcategory)         select distinct a.category, a.subcategory         animals         returning *         ) update animals ani set category_id = ins.id ins ins.category = ani.category , ins.subcategory = ani.subcategory         ;          -- have fk pointing new table,         -- can drop redundant columns. alter table animals drop column category, drop column subcategory;          -- show world select a.*         , c.category, c.subcategory animals join categories c on c.id = a.category_id         ;