How do I merge two database in mysql?

Question

0.00/5 (No votes)

See more:

Hi Everyone,
I am sorry but I am new in database, so my questions might look very easy for skilled DBA.

I have to create a new database for two companies merging:
Database A

Customer
(Customer ID(int), Name(varchar), Address(varchar), MoneyonAccount(decimal), Loanlimit(int), branchmanagerID(int) ).

Transaction
(Tranction ID(int), Transaction date(date), Customer ID(int), Loan granted date(date) ).

Transactiondetail
(Transaction ID(int), operation ID(int), Amount(decimal), bankcharge(decimal) ).

Operation
( operation ID(int), operationdescription(varchar),
 operationclass(varchar), chargeperoperation(decimal) ).

Branchmanager
( branchmanager ID(int), name(varchar), address(varchar), salary(decimal), bonus(decimal) ).

Database B

Customer
same as previous with different data and similar data such customer ID which is the primary key.

Transaction
same as previous but has added columns such as transaction motif and additional charge.

Transactiondetail
same as previous , just more data are added.

Operation
Operation ID has a different format (the previous is like 00215 and in this table it is like 46.

Branchmanager
same as in previous table

So my questions are how to create mapping of these tables?
How would look like the UML diagram of the new database?
How to combine these tables since some of them have different prmary keys and some columns are not similar or are missing?
If two primary keys are similar do I have to choose one?
How can all of the steps above done in PHPmyAdmin?

Thank you for your understanding

Posted 17-Nov-15 8:51am

Member 12147416

Updated 17-Nov-15 14:18pm

George Jonsson

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

George Jonsson · Accepted Answer · 2015-11-17T15:17:00

You have some different issues and here is one way you can approach them.

1. Different primary keys
You can create a new primary key and keep the old keys in a separate table.
By doing this you can still find an item with an old ID, but without messing up the table in use.

LegacyOperationLookup

OperationID   LegacyOperationID   Source
1             00215               Database1
2             46                  Database2

This depends on if you have the freedom to change the design.

2. Different columns
If you have to similar similar tables but one table has some extra columns, or if both tables have a column the other table is missing, you have at least two choices.
a. You can create a new table with the sum of all columns.
b. You can create an extra table containing the additional columns as legacy.

The trick here is to know if the extra columns are useful in the new organisation or not and also if the columns can be NULL or not.
If no one will use the additional columns it is better to keep them in a separate table.
If the additional columns cannot be NULL, you have to decide what to insert as empty values.

3. PHP Admin
See this link Merge two MySQL databases[^] or use the same phrase in Google.

4. UML Diagram
Regarding the UML diagram I will not do that for you, but just give you a pointer where you can start to learn it: TutorialsPoint: UML - Standard Diagrams[^]

I hope this gives you at least some help forward.