DB Data migration

Question

I have a database table called A and now i have create a new table called B and create some columns of A in table B.

Eg: Suppose following columns in tables

Table A // The one already exists

Id, Country Age Firstname, Middlename, Lastname

Table B // The new table I create

Id Firstname Middlename Lastname

Now the table A will be look like,

Table A // new table A after the modification

Id, Country, Age, Name

In this case it will map with table B..

So my problem is now i need to kind of maintain the reports which were generated before the table modifications and my friend told me you need to have a data migration..so may i know what is data migration and how its work please.

Thank you.

Answer 1

Update

I forgot to address the reporting issue raised by the OP (Thanks Mark Bannister). Here is a stab at how to deal with reporting.

In the beginning (before data migration) a report to generate the name, country and age of users would use the following SQL (more or less):

-- This query orders users by their Lastname
SELECT Lastname, Firstname, Age, Country FROM tableA order by Lastname;

The name related fields are no longer present in tableA post data migration. We will have to perform a join with tableB to get the information. The query now changes to:

SELECT b.Lastname, b.Firstname, a.Country, a.Age FROM tableA a, tableB b
WHERE a.name = b.id ORDER BY b.Lastname;  

I don't know how exactly you generate your report but this is the essence of the changes you will have to make to get your reports working again.

Original Answer

Consider the situation when you had only one table (table A). A couple of rows in the table would look like this:

# Picture 1
# Table A
------------------------------------------------------
Id | Country | Age | Firstname | Middlename | Lastname
1  | US      | 45  | John      | Fuller     | Doe
2  | UK      | 32  | Jane      | Margaret   | Smith

After you add the second table (table B) the name related fields are moved from table A to table B. Table A will have a foreign key pointing to the table B corresponding to each row.

# Picture 2
# Table A
------------------------------------------------------
Id | Country | Age | Name
1  | US      | 45  | 10
2  | UK      | 32  | 11

# Table B
------------------------------------------------------
Id | Firstname | Middlename | Lastname
10 | John      | Fuller     | Doe
11 | Jane      | Margaret   | Smit

This is the final picture. The catch is that the data will not move from table A to table B on its own. Alas human intervention is required to accomplish this. If I were the said human I would follow the steps given below:

1. Create table B with columns Id, Firstname, Middlename and Lastname. You now have two tables A and B. A has all the existing data, B is empty .
2. Add a foreign key to table A. This FK will be called name and will reference the id field of table B.
3. For each row in table A create a new row in table B using the Firstname, Middlename and Lastname fields taken from table A.
4. After copying each row, update the name field of table A with the id of the newly created row in table B.

The database now looks like this:

# Table A
-------------------------------------------------------------
Id | Country | Age | Firstname | Middlename | Lastname | Name
1  | US      | 45  | John      | Fuller     | Doe      | 10
2  | UK      | 32  | Jane      | Margaret   | Smith    | 11

# Table B
------------------------------------------------------
Id | Firstname | Middlename | Lastname
10 | John      | Fuller     | Doe
11 | Jane      | Margaret   | Smith

5. Now you no longer need the Firstname, Middlename and Lastname columns in table A so you can drop them.
6. voilà, you have performed a data migration!

The process I just described above is but a specific example of a data migration. You can accomplish it in a number of ways using a number of languages/tools. The choice of mechanism will vary from case to case.

Answer 2

Maintenance of the existing reports will depend on the tools used to write / generate those reports. In general:

1. Identify the existing reports that used table A. (Possibly by searching for files that have the name of table A inside them - however, if table A has a name [eg. Username] which is commonly used elsewhere in the system, this could return a lot of false positives.)
2. Identify which of those reports used the columns that have been removed from table A.
3. Amend the existing reports to return the moved columns from table B instead of table A.

A quick way to achieve this is to create a database view that mimics the old structure of table A, and amend the affected reports to use the database view instead of table A. However, this adds an extra layer of complexity into maintaining the reports (since developers may need to maintain the database view as well as the reports) and may be deprecated or even blocked by the DBAs - consequently, I would only recommend using this approach if a lot of existing reports are affected.