Database Refactoring
I have been given two Data models, for the sake of simplicity let me call these models A and B. Now each of these models have a bunch of tables and preexisting relationships. Both of these models try to solve the same basic problem. Now my task is to refactor Data Model B into something closely resembling Data Model A. So in layman's terms, Data Model B should look like Data Model A while incorporating the additional tables that are essential to drive the application that uses Data Model A.
What would be the most effecient way of going through this process knowing the fact that Model B has close to 75 tables and Model A has 63 tables.
Any help will be appreciated.
# 1 Re: Database Refactoring
Create queries for database B whose resulting recordset looks like a corresponding table in database A. Include queries for any additional tables in database A. Then use these queries as a source for append queries that load the data into database A tables. If you are going to merge the data from database A and B, then to be safe, create an empty version of database A, and run your append queries into that database. This way you can check the final results before doing it to database A.
# 2 Re: Database Refactoring
Thanks a lot for your response Ron. Being relatively new to Data Modelling, my question may seem a little naive but I hope you will forgive me for that.
So pretty much what I was saying was my Data Model A has something as follows:
PeopleTable:
PeopleID (PK)
PeopleName
PeopleAddress
PeopleLogin
Now my DataModel B has something as follows:
EmployeeID (PK)
EmployeeName
EmployeeAddress
EmployeeLogin
EmployeeDept
Data Model A has an Employee table that has the PersonID as a FK. Data Model A with its combination of Person table and Employee table serves the same basic purpose as DataModel B with just an employee table. Now I want to transform the employee table of B into a structure resembling that of A. My example here is extremely simplistic, the relationships are a little more complicated in the actual DB.
What I am doing is manually doing through the diagrams of models A and B and writing down the similarities and differences between the two models. This process is extremely tedious and chewing up a lot of my time. What I figured is once I get the similarities and differences straightened out, I can come up with the intermediate data model that is a hybrid of both A and B.
I am not sure whether I am making a whole lot of sense here, but pretty much to put it real bluntly, DataModel B is an incoherent mess while DataModel A is a pristine data model, according to the industry standards. Now the bosses want Model B to be like A with the additional features of model B in it. One more thing..the table names, field names are completely different. There is no similarity in the naming conventions between the two tables.
I will really appreciate all the help I get.
# 3 Re: Database Refactoring
It looks to me like everything is basically identical, You have a primary key PeopleID and a matching field in B, (EmployeeID), you have Name, Address, and Login. So all you need to do is add a Department to Model A, and maybe a Yes/No field, otherwise known as a boolean data type, that flags weather or not this person is an employee. Also if you have other tables that reference the Model B table and you need to convert them to now reference the Model A table the you should store the EmployeeID in the A table as well. Then you can create update querys that can be used to fill in the new links to the PeopleTable as well. Later after everything has been converted over to use the PeopleTable you can remove any EmployeeIDs from the PeopleTable as well as the other tables.
So New Version of PeopleTable would look something like this:
PeopleTable:
PeopleID (PK)
PeopleName
PeopleAddress
PeopleLogin
PeopleDept
PeopleIsEmp
EmployeeID (FK)
Next you would add the PeopleID Field to any other tables that you want to now link to the PeopleTable. These other tables would be the ones that already have EmployeeID in them and you need them to now look at the PeopleTable for the Employee. You would then fill in the PeopleID field in these other tables by using an Update Query that links the other table to the PeopleTable by the EmployeeID fields.
Also when you build your Append query to add the records from the Employee Table to the PeopleTable you can use a Calculated Field to force the PeopleIsEmp field to true for all of the records comming from the Employee table, which would be employee's so they all should have their PeopleIsEmp Flags set to true.
If you want I can do a little mockup of this in an Access database and attach it to my next post. Just let me know.
