Importing data from Access to MySQL
Hello experts!
At work we have been using an MS Access database to track employee hours. This database was build in Access 95 in 1996. I have been tasked with creating a new system that isn't inherently internal (meaning our other offices would not need to remote in to use it). I've decided, based on my strengths, to use PHP / MySQL to do this.
Now the first problem is trying to copy all the data from Access to MySQL so we don't have to continue to maintain the old system (if we can avoid it). I'm unsure the best way to do this as I'm not very versed in Access and the queries running it aren't following general SQL standards. To make matters worse, the Access database wasn't even developed to follow 1NF so I can't in good conscience use the same schema as Access. Even worse there are about 70 tables in the Access db, however some of them are empty, and there are series of TableName1 TableName2 etc so I'm not even sure which ones are being used! Information is duplicated in so many places I can't follow the logic of the schema (if there is any)
My question to you guys is this: What is the best way to copy this data into the new system? How would you go about analyzing an older DB like this to make sure I'm copying over the correct current data?
At work we have been using an MS Access database to track employee hours. This database was build in Access 95 in 1996. I have been tasked with creating a new system that isn't inherently internal (meaning our other offices would not need to remote in to use it). I've decided, based on my strengths, to use PHP / MySQL to do this.
Now the first problem is trying to copy all the data from Access to MySQL so we don't have to continue to maintain the old system (if we can avoid it). I'm unsure the best way to do this as I'm not very versed in Access and the queries running it aren't following general SQL standards. To make matters worse, the Access database wasn't even developed to follow 1NF so I can't in good conscience use the same schema as Access. Even worse there are about 70 tables in the Access db, however some of them are empty, and there are series of TableName1 TableName2 etc so I'm not even sure which ones are being used! Information is duplicated in so many places I can't follow the logic of the schema (if there is any)
My question to you guys is this: What is the best way to copy this data into the new system? How would you go about analyzing an older DB like this to make sure I'm copying over the correct current data?
Well if the data is not normalized, then you can import it, but you still have to "Fix" it.
There is no set way to "fix" un-normalized data.
Fixing un-normalized data will require *You* to understand what this data was trying to represent.
For example, a separate custom system will be required to get the DB from "0NF" to 1NF.
Then another to go from 1NF to 2NF, then another for 2NF to 3NF...etc
Not counting all the exceptions and possible inconsistencies in the data.
In other words, with 70 tables, there are no easy answers here.
Let's see what other Experts may contribute.
There is no set way to "fix" un-normalized data.
Fixing un-normalized data will require *You* to understand what this data was trying to represent.
For example, a separate custom system will be required to get the DB from "0NF" to 1NF.
Then another to go from 1NF to 2NF, then another for 2NF to 3NF...etc
Not counting all the exceptions and possible inconsistencies in the data.
In other words, with 70 tables, there are no easy answers here.
Let's see what other Experts may contribute.
ASKER
My apologies for not returning sooner, I got absolutely swamped with work today.
@COBOLdinosaur The problem isn't the simple export, and the import, but rather the step in between where I have to alter the data from 0NF to 3NF
I know its going to require figuring out the data, I'm just not sure how to do that. I can't seem to figure out what tables are actually being used. For example there are 7 different tables that hold the similar employee information, with as few as 16 rows to as many 78. The queries are just mind boggling puzzling.
Another question is should I just pretend Access doesn't exist until after I get the new application working and then try to figure out how to the Access data fit into the new system or should I keep the Access data in mind in developing the new system?
@COBOLdinosaur The problem isn't the simple export, and the import, but rather the step in between where I have to alter the data from 0NF to 3NF
I know its going to require figuring out the data, I'm just not sure how to do that. I can't seem to figure out what tables are actually being used. For example there are 7 different tables that hold the similar employee information, with as few as 16 rows to as many 78. The queries are just mind boggling puzzling.
Another question is should I just pretend Access doesn't exist until after I get the new application working and then try to figure out how to the Access data fit into the new system or should I keep the Access data in mind in developing the new system?
Did you read my post above...
https://www.experts-exchange.com/questions/27654283/Importing-data-from-Access-to-MySQL.html?anchorAnswerId=37783586#a37783586
This will not be easy.
Also not that we don't have any access to your data so this makes it all the more difficult.
How much do you know about Normalization...?
https://www.experts-exchange.com/questions/27654283/Importing-data-from-Access-to-MySQL.html?anchorAnswerId=37783586#a37783586
This will not be easy.
Also not that we don't have any access to your data so this makes it all the more difficult.
How much do you know about Normalization...?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@boag I know quite a bit about normalization I like to think. Its kind of a pet peeve of mine. In fact my DBA friend tells me all my projects have followed 3NF completely. And since I design my DBs before I start trying to work with any data, I'm just having a hard time following the so called "logic" of this older database. Was hoping there was kind of a general solution that would need modification to fit my needs (like analyze each table, and fix columns with multiple values into their own tables, or whatever)
@COBOL So basically I shouldn't build a freestanding system and try to make the old data fit, but rather somewhere in between? I didn't think about using temporary tables in between. That will make it much easier.
Last question: I thought about duplicating the Access DB to test on, such that I remove tables with similar names and test if it still works or not. Does this sound like an ok approach to at least try to limit the amount of redundant data I work with? I worry I may not test every aspect and may lose real data.
@COBOL So basically I shouldn't build a freestanding system and try to make the old data fit, but rather somewhere in between? I didn't think about using temporary tables in between. That will make it much easier.
Last question: I thought about duplicating the Access DB to test on, such that I remove tables with similar names and test if it still works or not. Does this sound like an ok approach to at least try to limit the amount of redundant data I work with? I worry I may not test every aspect and may lose real data.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Cd&