For data harvesting, and for reporting, it's best to not normalize your data but to store it all in a single table. After, you can normalize it (or for data reporting, leave it as is).
Next time, a single table for harvesting, a merge of all data, then normalize into other tables :) On Thu, May 20, 2010 at 12:47 PM, Mac Newbold <[email protected]> wrote: > Yesterday at 5:53pm, Jonathan Duncan said: > > > Here is a database conundrum. I have two databases with identical > > tables (same structure different data). They have been on different > > sites though collecting data from different groups of people. I now > > need to merge that data. There is one table that relies on the 'id' key > > of another table. So the id's well need to be changed, and the other > > table updated to match. Any ideas? > ... > > I am thinking I may just write a PHP script that will select from > > db1.table1, insert it into db2.table1 and then check if there is an > > associated row in db1.table2 and if so insert that in db2.table 2 with > > new 'id' key from last insert on db2.table1. > > > > Thoughts? > > I'll second your solution - php script to pull from one, insert into the > other, renumbering with new IDs in dependent tables as necessary. You're > lucky there aren't more cross-table dependencies and foreign keys. > > And remember the moral of the story: > > > They have been on different sites though collecting data from different > > groups of people. > > Next time don't do that. > > (If you really need two separate sites collecting it, it is generally best > to have them both put it in the same database to begin with, either via an > API or just by sharing a database, perhaps with each source marking its > entries as to where they were submitted.) > > Thanks, > Mac > > -- > Mac Newbold Code Greene, LLC > CTO/Chief Technical Officer 44 Exchange Place > Office: 801-582-0148 x102 Salt Lake City, UT 84111 > Cell: 801-694-6334 www.codegreene.com > > _______________________________________________ > > UPHPU mailing list > [email protected] > http://uphpu.org/mailman/listinfo/uphpu > IRC: #uphpu on irc.freenode.net > -- Take care, William Attwood Idea Extraordinaire [email protected] _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
