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 !

Thanks,
Yves

if by "refactoring" you mean how do you get the data in the single flat table into a new set of properly normalized tables, you would do that by running a series of simple queries

do your new design right in Access, and then take a screen shot of the relationships if you want us to provide comments

You should not need much more than a rudimentary grasp of SQL (probably...).

http://www.w3schools.com/sql/default.asp

pootle flump ur codings are working excelent.

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.

Yves

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.

The standard query tool is visual.

Also, there's a table-splitting wizard or something in Access, but I've never used it. (It has to guess what your dependencies are based on the data, but I believe you can correct it.)