如何在mysql中合并两个数据库？ [英] How do I merge two database in mysql?

问题描述

大家好，

对不起，我是数据库的新手，所以对于熟练的DBA来说，我的问题看起来很容易。



我必须为两家公司合并创建一个新的数据库：

数据库A

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) ).

数据库B

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

所以我的问题是如何创建这些表的映射？

如何看待新数据库的UML图？

如何组合这些表，因为它们中的一些具有不同的prmary键，而某些列不相似或者是缺少？

如果两个主键相似，我必须选择一个吗？

如何在PHPmyAdmin中完成上述所有步骤？



感谢您的理解

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

推荐答案

您有一些不同的问题，这是您可以采用的一种方式。



1.不同的主键

您可以创建一个新的主键并将旧键保存在一个单独的表中。 />
通过这样做，您仍然可以找到带有ol的项目d ID，但没有弄乱正在使用的桌子。



LegacyOperationLookup

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

这取决于您是否可以自由更改设计。



2。不同的列

如果你需要类似的类似的表，但是一个表有一些额外的列，或者如果两个表都有一个列而另一个表缺少，那么你至少有两个选择。

a。您可以使用所有列的总和创建一个新表。

b。您可以创建一个包含附加列的额外表格作为遗产​​。



这里的技巧是知道额外的列是否在新组织中有用，以及是否列可以是NULL也可以不是。

如果没有人会使用附加列，最好将它们保存在一个单独的表中。

如果附加列不能为NULL ，你必须决定插入什么作为空值。



3。 PHP管理员

查看此链接合并两个MySQL数据库 [ ^ ]或在Google中使用相同的词组。





4。 UML图

关于UML图我不会为你做那个，但只是给你一个指针，你可以开始学习它： TutorialsPoint：UML - 标准图 [ ^ ]



我希望这能给你至少一些帮助。

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.

这篇关于如何在mysql中合并两个数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！