hey all,
I have two tables like that from an old php project:
artists(id,name)
albums(id,artist_id,album_name)
and I need to transfer the data of this database to three tables that
look like this:
artists(id,name)
albums(id,name)
artists_albums(album_id,artist_id)
Well, for starters, you probably want albums_artists if you intend this to be a has_and_belongs_to_many table since "albums" comes before "artists"
rename artists to old_artists
rename albums to old_albums
insert into artists select * from old_artists
insert into albums select id, album_name as name from old_albums
insert into albums_artists select id as album_id, artist_id from old_albums
then if all your test pass you can drop the old_* tables
You didn't say what database so I'm leaving this generic. (and migrations are great for this sort of thing, too)