What's the most efficient way to combine tables when combining databases

I have two databases (db1 and db2) that used to be used by two different programs, and now I am combining them (both the databases and the programs), so, db2 is going to be part of db1. So, I know how to copy over the tables from db2 to db1, but I have a table that contains a list of users.

I want both of the programs to access the same list of users, so I need to combine the two users tables. Now, the problem is that db2's user table has different columns than db1's. So, how should I do this? Should I just add more field开发者_StackOverflows do the db1 table? Or should I just copy over as much relevant data as possible, and then make another table to hold the fields that aren't preset in db1's user table?

---

What you should look at first and foremost is what those tables mean to the business. Column names alone won't tell you that. If both tables have a column name "lastaccesstime" that fact by itself does not say ANYTHING about what the contents of those respective columns mean to their respective users.

This kind of exercise is not just the mechanical operation ("copy over tables", "add more fields") you seem to think it is.

---

Depending on your needs, either one could be an acceptable solution.

If the extra fields only exist for certain users, then maybe go with the second option. But if all of the fields should exist for every user, then I would just say add the new fields to your user table, then worry about back-filling the data for the other users.

---

Create a VIEW that combines both tables.

---

I would start by defining what the combined data base would look like if you weren't starting from existing data bases. This includes considering normalization, indexing, column types, etc. Then see if there's an easy mapping from each of the current data bases to the new structure. If so, just write SQL to do the mapping. If not, then you have two choices. You can write more complicated SQL (or even a procedure in some other language) to map the data. Alternatively, you can consider modifying your ideal target data base schema to make it easier to import the legacy data. Finally, once you've decided on an approach, you can decide if it's easier to modify one of the existing data bases or to create an entirely new one and bring data from both data bases into the new one. (If you do the latter, you can then rename it to one of the old ones and call it a modified version, in case this will help maintain compatibility with existing application code.)