Insert from 1 table to 2 tables

[nagrap]

Hi,

I'm quite new to php and not the greatest of coders, so urgently require your help.

I want to be able to take data from an un-normalised table and load into 2 normalised tables as below:

1) Table 1 - All Music Tracks (Un-normalised table)

Album Name
Artist Name
Track Name
Track Length
etc.

2) Table 2 - Albums Table (Normalised table)

ID
Artist Name
Album Name
etc.

3) Table 3 - Tracks Table (Normalised table)

ID
Track Name
Album ID (foreign key to Albums table)
etc.

Can someone please provide some sample code as a starting point?

The All Music Tracks table includes both the album name and track name in each row, but I only want the album appearing once in the Albums tables, so I need a way of getting all album names, then inserting into the Album table, then inserting the tracks into the Tracks table at the sametime.

I will have approx. 2000 records to process, so should I take this into consideration as part of the code, or is 2000 recs considered small?

Much Appreciated,

PD

[bradgrafelman]

You might even go one step further, since your Albums table still has duplicate data; you can separate the "Artist" information from the Albums table giving you three normalized tables.

Assuming you did that, just think through the logic of what you need to do; when retrieving each row from the un-normalized table, you need to:

1. Query the Artists table to get the Artist ID for the given Artist Name. If no rows are returned, INSERT the artist info and use the ID assigned to the new row.

2. Query the Albums table to get the Album ID for the given Album Name. If no rows are returned, do the same as above.

3. Insert the track information into the Tracks table, using the Album ID from above.

As far as linking the artist information to the albums/tracks, you might consider doing this in the Tracks table, since one album could contain tracks from multiple artists (think "Top 10 Hits from 2010" type of albums).

nagrap wrote:

is 2000 recs considered small?

As far as the RDBMS software is concerned? It's minuscule, considering it could quite easily handle millions of rows.