Page 1 of 1

schema matching for multiple databases

Posted: Sat Jan 29, 2011 12:55 pm
by sam2018
Hi,

I have been working on schema matching for multiple databases and trying to implement it in php. I have two databases ; one called as bookstore and another bookstore_warehouse. I need to do a schema matching between these two databases. I tried to use functions like mysql_tablename(), mysql_num_fields() and retrieved table names and column names of both databases and kept them in array. But i am little bit stuck on how to compare those table names and field names. Can you provide me some suggestions on it Or may be what would be effective way of comparing databases. Thanks

Re: schema matching for multiple databases

Posted: Mon Jan 31, 2011 10:33 am
by Jade
What exactly are you trying to compare? For instance, do you want to know which books are in both databases? Sorry, I'm just not sure why you'd be trying to compare schemas from two different databases... that's kind of like trying to compare apples and oranges.

Re: schema matching for multiple databases

Posted: Tue Feb 01, 2011 9:02 am
by sam2018
I am trying to compare where the fields for eg ISBN that exist in Books table of bookstore database matches with that of bookstore_warehouse. In bookstore_warehouse db ISBN may be represented differently like book_ISBN . So my main objective is to see how many fields of tables of one database with fields of other database. It is related to some sort of database integration.

Re: schema matching for multiple databases

Posted: Tue Feb 01, 2011 10:40 am
by Jade
In bookstore_warehouse db ISBN may be represented differently like book_ISBN . So my main objective is to see how many fields of tables of one database with fields of other database. It is related to some sort of database integration.
You mean the ISBN may have a different field name? You would need to know the name of both fields from both databases before you can do any sort of comparison on them. Matching the fields based only on their schema data will be extremely challenging. There's no way to tell if one varchar(50) field is the same as another varchar(50) field in another database -- although you could produce a list of all fields with matching types and lengths -- that's probably the best you'll get but it's not a great way to try to match up fields.

This is really something you should do yourself instead of writing a script to do it for you. Especially if the data from both tables isn't guaranteed to even be in the same format.....