CODEWITHSUNDEEP
sqldatabasedatabase-design
zerminator
zerminator

Reputation: 11

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 fields 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?

Upvotes: 0

Views: 77

Answers (4)

Erwin Smout
Erwin Smout

Reputation: 18408

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.

Upvotes: 1

Ted Hopp
Ted Hopp

Reputation: 234795

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

Upvotes: 0

Lost in Alabama
Lost in Alabama

Reputation: 1653

Create a VIEW that combines both tables.

Upvotes: 0

Jason Dean
Jason Dean

Reputation: 9615

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.

Upvotes: 0

Related Questions