I have "inherited" an Alumni DB in MS-Access, which is actually nothing more than a flat table with approximately 2000 records and 35 columns. The Alumni directory has been maintained for years by a single person on a voluntary basis, but he is leaving abroad shortly. Our Alumni directory is now supposed to become the back-end of a full-fledged web application (e.g., using MySQL). However I am more an application developer than a DB expert. Now, assuming that I have normalized this flat table and put down an appropriate DB schema on paper, can anyone provide some info as how to actually do the refactoring process ? Any pointers to useful (and free) tools, tutorials, articles, etc. would be greatly appreciated !
Ok, thanks! For a moment I thought that visual tools could be used to deconstruct the flat table etc. (with SQL in the background). So I will brush up my knowledge SQL a little, and may come back if any question would arise in the process.
Issue 1: If you're using surrogate keys, you'll want to add an autonumber field to your table. I'm not sure if Access will fill it for you, if not, copy the schema of the table to new_table, add the autonumber field, and run:
INSERT INTO new_table SELECT * FROM old_table
Now you've got all your rows numbered.
Issue 2: Futzing with types. Rather than creating the tables from scratch, I'd set up SELECT queries to start with. Then just copy those queries to actual tables.
That way you've got an actual table to work with.
Issue 3: Regression. Basically, you want to make sure that you don't corrupt any data. The best technique is to create some queries that get statistics on your data. For example, if you have 800 people to start with, you need to end up with 800 people. Get averages of stuff like birthdays. You can also get the average length of different fields. Those numbers are very sensitive, so if you mess up one record, it's likely your average will be off just a little bit. If you've accidentally truncated a field, the max length will definitely be off.