Re: Conceptual problem between many independent data bases! Please GURUs for any feedback!!!

[Stefan P]

I will restate the problem, make another example and clear out those parts that you have point out!

The problem is sharing data between systems (WEB SITES, ERP, CRM AND ETC.) that are absolutely independent; how to do it? ..metaphorically.. it is if 30 people from different nations need to communicate by speaking... SHOULD EACH PERSON LEARN ANOTHER 29 LANGUAGES ...OR EVERY PERSON SHOULD LEARN ONLY ONE LANGUAGE THAT WOULD BE THE OFFICIAL FOR THE GROUP!!???

The answer might be very streighforward, but not among ITs I have questioned :D ..

The example:

We have two web-sites - A and B. web site A has a data base - db_A with a table with company names, lets call it tb_names_A, the company names are updated regularly.
The second web site - B is having db_B also with a table for company names (tb_names_B) and to this table are referenced few another tables like activities, galleries, blogs etc.
website_B receives company names each day from website_A via dump and imports it in its own data base.
1st case>> A user changes the name of a company and the change is stored in a temporary table temp_names_B and is sent to website_A, website_A updates the field
and sends it back to website_B. After receiving it website_B updates the company name and erases the temporary table. When data is send, all IDs are included.
2nd case>> A user creates a company name on website_B and attaches to this name an activity, gallery, blog and etc., the new company name is saved in a temporary table and sent to website_A (the row of the data that is sent looks like this: ID_names_B |fieldbrake| ID_names_A (at this stage this field is always 0)|fieldbrake| company name|). Website_A receives this data, saves it, gives the row its own ID (ID_names_A is changed to non zero) and sends it back to website_B. Website_B receives it and finds that the row has a filled ID_names_B, hence this row originates from a user in website_B and has references to activities, gallery, blog and etc.-- this is the most important thing-not to lose the already created referances!!!!!!!!!!!!


So far so good. The two websites communicate by sharing their keys.
But if we add another 10 websites, that following the logic above we would need to add 10 more columns that would carry the keys in each website. AND if add another field that would be shared by all websites and can be changed from a user in any of the websites, than we should add to this second field another 9 columns for keys.
I am having this problem for a long time and I when talk to ITs who are into data bases things, hearing the case with two websites they say thats how it should be done!!! ..but when I tell them the case with 10 websites ..they say that having 10 columns of keys for each field is stupid!!!!! I think so too!!!


Now I clear out things from the previous post:

"web services running on their own data-bases with own architecture"
--- meaning that each website has its own unique data base structure suited for its own needs

"common data between each other"
--- a data which context is equal for any of the data bases
for example one website deals with financial data for companies and the second deals with products of companies, these two websites share the company names which is the common data.

"each data field could be created, modified or deleted in each DB, the change is stored in temporary table and is communicated to the DB that the field was originates from"

If the sharing was just simple, as far as just sending data that could not be changed the problem would me more simple, db_A would just send data and users would just use is, nothing more!

".... the change is stored in temporary table and is communicated to the DB that the field was originates from"
--- Because we give the possibility to user to change data or create new in any of the websites, this change should be communicated to the rest of the websites!!!

"each independent db should know who is the responsible master db for the field"
--- this is an appropriate naming, since the change in a field is always communicated to a data base that sends this context type of field to the rest dbs.

"the master DB should share the update of the field to all other dbs as new and the temporary erased"

--- if the field has been updated, the website that hosts the user that have made the change would not need the keep the change in a temporary table, because the field has been updated and is received anyway, why use the temporary even if it is the same data.

"make each db responsible for certain table, but not every db for any field in the table"

--- db_A stores financial data and is responsible for revenue table, db_B stores products and is responsible for table with product prices, db_C stores company names and is responsible for table with company names. db_C sends company names to db_A and db_B. If a user changes a company name in db_A, the new name is temporary stored, sent to db_C. db_C updates the name and sends it to db_A and db_B, hence db_A will use the data that it receives which includes the new name of the company, but not use the new name in the temporary table ---this makes each db responsible for certain table.
If for example the new name was communicated by db_A send to all other dbs the new name than db_A would be responsible for only this field and db_B would receive company names from db_C and only one company name -the new one from db_A. In case a user changes a name in db_B than db_B becomes responsible for this field --- that is what I mean field responsible, which is no way to happen because if we have 10 db each throwing common data to all other it would create such a uncontrollable mesh.

Hope I have shed some light.