Synchronizing 2 tables with different field structure

[nickcwj]

Greetings every noble people out there,

 

Can somebody be kind enough to look into my question... I\'ve provided the links to my question since this forum does not support file attachment... here ya go:

 

1) http://www.dbforums.com/showthread.php?pos...115#post3486942

 

2) http://www.sitepointforums.com/showpost.ph...286&postcount=5

 

The two links actually refer to the same question but link 1 has a jpg photo.... hope to hear from you guys soon... thnx.

 

Regards,

Nick

[pauper_i]

Hi Nick!

Not wishing to pour gasoline on your personal hell, but just an observation: If you had picked up a MySQL book on the day you posted in the first of those forums you could have answered your own question by now. 11 days later, and you are still looking for someone else to answer the questions for you!

 

The biggest problem you are facing is that you are providing too much information of the wrong type, and not enough of the right type:

 

Don\'t bother explaining about the sybase crossover - a lot of MySQL programmers will see that and skip to the next question. Get it working in MySQL then worry about the changeover afterwards.

 

You are apparently trying to synch up two tables: please define what you mean by \'synch up\' - same info in both?

 

Replicate any changes in one to the other??? Why? Why not just store the changes one time and access that info from both tables?

 

But when it is time for data synchronization, all updated data would be replicated into EntityRelocation_tbl, which has a different data structure as compared to McInto table

If this is a replicated table, then why is the data structure different?

 

I\'m sorry, but there are too many questions left open, it would take a book to write out all the possible answers. Please sit down and rethink your problem, see if you can define it for us as simply as possible, outlining the information we need. It would appear that the basic fundamental issue here is database design - I realise that having had this dumped in your lap this is probably not a good time to adddress this, but it would probably be easier to get the databases redesigned now than to fudge around the issues and have to cross many more bridges further down the road.

 

Hope that helps!

 

D

[nickcwj]

Hi pauper_i,

 

Thnx for the prompt reply. However, I do apologize if I had not outlined the reasons behind all this intricacy. Allow me to explain:

 

1) Mcinfo table is actually a table currently running real-time in the production line in my factory. This is the table where the technicians would actually update whenever a machine/equipment is relocated/swapped... this table is practically a subset of EntityRelocation_tbl.

 

2) EntityRelocation_tbl on the other hand is a newly designed table with a full feature set of fields... something like a mother to Mcinfo_table... but this table is NOT running real-time in production; it is basically like a backup/reference table, but it needs to have the latest data as updated in Mcinfo table... meaning whatever changes made in Mcinfo table, must be synched and updated in EntityRelocation_tbl.... it is somewhat like Microsoft\'s Active Directory replication but in this case, the directory structure is rather different.

 

3) The problem is the flow of updating data in Mcinfo table is not very well favourable to my boss. He prefers to have a super table (EntityRelocation_tbl) which has a slightly different method of updating data.... hence the reason behind why these 2 tables have different field/data structure... if u take a look at the diagram in the attached zip file (located in the other forums i posted earlier), the data flows \"vertically\" in Mcinfo table, while the data flows in a \"zigzag\" manner in EntityRelocation_tbl ... take note of how the numbers such as SA23, SA19, and SA08 flow in the two tables.

 

 

** In other words, my boss prefers \"zigzag\" as compared to \"vertically\", and he can\'t change the way Mcinfo table works coz it\'s already running for a long time in production.... not a feasible idea to restructure that table.

 

 

4) To sum it up, it is something like getting 1st & 2nd, and 2nd & 3rd rows from Mcinfo table and putting it to 1st and 2nd rows respectively in EntityRelocation_tbl.

 

I do hope you can understand the message i\'m trying to convey here... i know it sounds a tad confusing for most people who replied my forums but I would really need help on this one.... i\'ve been doing a lot of direct INSERT ... SELECT, UPDATE, etc... kinda sql codes ... but this one here really takes the cake.

 

I can\'t change the database design as it is NOT designed by me but the folks at my company\'s hq... so I have no say in the db design. Just follow orders.... Sigh.... life sucks huh?

 

All I really need is somebody to start me off with the full set of code examples.... and then I can learn from there. I thank you in advance for your kind assistance.

 

Many thnx,

Nick