Normalizing an already populated database

[atheistrical]

I have been in the process of learning MySQL and have learned a lot from CodingForums.com, special thanks to all the expert coders around like Old Pedant.

I have prepared the skeleton of a normalized database from what I discovered as a very bad single tabled database design. I have come across this issue now, where I will also mention the measures I have taken to resolve it.

Issue : The database to be normalized is already populated with thousands of rows of data. I wish I could export the data column-wise.
Resolution Attempted : I made copies of the database, deleted the columns which weren't required to be exported and then exported the single-columned table. I did this for every column.
Problems : The newly created database (i.e. the normalized scheme) has linking columns (i.e. foreign keys) which were absent in the previous single-tabled database.

I seek your advice on this please!

[oesxyl]

what is more painfull for you, xml/xslt or sql? :)

best regards

[bazz]

you could make the foreign keys dependent on the primary key.

which client are you using? phpmyadmin heidisql etc?
you could try this in the command line. it makes a constraint to tie a foreign key to its parent column.

alter table table_name
add constraint name_this_fk
foreign key column_name
references table_name(column_name)



I use the naming convention for 'parent table' _ 'fk table' _ 'fk'. It is always better to gvie a name so that if you ever need to remove it you can use that name to make it much easier.

Don't forget to change the values I have shown eg table_name (to be that of your table, column_name (to be the col name with the fk) and 'table_name(column_name)' to be the location of the parent key

bazz

[Old Pedant]

I think I would have to see some specific examples of what you had and what you now have.

I think I see where oesxyl is going with his possible solution, so answer him, too.

[Old Pedant]

Re Bazz's comment: Be sure to use INNODB engine. The MYISAM engine will *NOT* enforce referential integrity.

[bazz]

oops; I forgot that bit.