MySQL practical normalisation on large single table

Question

I'm relatively new to PHP MySQL and have tasked myself on learning with the "hands on" approach. Luckily, I currently have a (very) large database all relating to coin data with one table to work with. It currently has the following columns (each row representing a single item [coin]):

Group ItemNo ListNo TypeCode DenomCode PeriodCode ActualDate SortDate CostPrice SalePrice Estimate StockLevel DateEntered DateSold Archived ArchiveWhenSold Highlight KeepSold OnLists NotForSale Proof StockItem OnWeb Cats Ref1 Ref2 Variety Picture Description TypeName TypeHeading DenomName DenomHeading DenomValue PeriodName PeriodHeading PeriodStartYear PeriodEndYear

The groupings for new tables are relatively obvious:

Period:

PeriodCode PeriodName PeriodHeading PeriodStartYear PeriodEndYear

Denom:

DenomCode DenomName DenomHeading DenomValue

Type:

TypeCode TypeName TypeHeading

All the rest, under a Coin table:

Group ItemNo ListNo TypeCode ActualDate SortDate CostPrice SalePrice Estimate StockLevel DateEntered DateSold Archived ArchiveWhenSold Highlight KeepSold OnLists NotForSale Proof StockItem OnWeb Cats Ref1 Ref2 Variety Picture Description

So I'm looking to normalise the table into the tables specified. I know that i'm looking at JOINs but am wondering the best way to go about it. Do I create a new table FIRST with each data group (Denom, Period, Type) and THEN insert the data using a JOIN statement? Or is there a way to create new tables "on the fly" with a JOIN statement. I've got a honking great book open here and am following along nicely the section on MySQL and also looking through this site, but haven't been able to figure out the "correct" way to do this.

The reason I ask here for some knowledgable advice is that i'm a little unsure about how to maintain the "relationships" and keys etc. i.e If I create a table called "Denom" and populate it with all the distinct items from all the current tables data and also have it create a unique primary key, how to I then insert the reference to this new primary key from the Denom table into the main Coin table (under a new item DenomID) so that they match up?

I basically need to split this table up into 4 separate tables. I've tried this using Access 2007's table analyzer wizard and it looked promising for a n00b like me, but there was so much data, it actually crashed. Repeatedly. Probably for the best, but now I need to know some best practice, and also HOW to put it into practice. Any advice/help/relevant links would be greatly appreciated.

Thanks!

Answer 1

Create the tables first, don't forget to add a foreign key field to all the child tables that contains the Primary key from the main table (also each new table must get a primary key), so that you can join the tables. If you don't have a primary key, you need to create one before doing anything else.

To put the data into the tables is a simple insert

insert tableb (field1, field2)
select field1, field2 from tablea

You will join to get the database out, so rememebr to create indexes on the new tables especially onthe foreign key field.