I'm in the process of normalizing some aspects of a database that already contains data. The database had grown unusable and is a poster child for the need for normalization. To expand it and make it more functional, I'm in the process of redesigning various aspects of it. I'm an aerospace engineer who ended up "owning" the database, so my SQL is not quite up to par.

To start, I need a way of doing some simple data reconfiguration. In particular I have a table with a column that contains many repeating values. Obviously this is a prime candidate for normalization (not only philosophically, but I need to implement some enhancements).

How do I go about creating a new table with the unique values found in the column of the first table, and how do I point the foreign keys of the first table to the primary keys of the new table?

Thanks!
Carlos

modifying an old db that isn't properly normalised can be a pig. in part thats becuase people need to keep using the old applciation whilst the new one is bein developed.

its not impossible, but it can be tricky

first complete your design....

then copy your existing db, then create the new table. take note of any changes to the db (New tables, and changes, any deletions and so on... and document thise changes. in an ideal world run these chanegs as a SQL query (there various sub dialects to SQL, thesres the data manipulation language (DML: the select, insert update and so on), there the Data definition language (DDL: create, alter and so on)
if you save your DDL queries as you go on then you run thiose queries when ready on another copy of the live db as part of the implementation phase

the data transfer shold be realtively straight forward
use an "insert" query, using a sub query that actually selects the data from the old table