Unanswered: Discover relationships between existing tables of a database
I am right now working on a project that has a database with over 100 tables in a database. Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables. Now I need to remodel the database such that the database is more structured and normalized. I don't have much knowledge about the database design since it is a 2 year old application and the person who developed the database is now gone. I know remodelling the database would require knowledge of the existing database and business rules.
I was wondering if there are any tools that could suggest or discover relationships between tables. For eg. Lets say there are two tables named 'Customer' and 'Order'. I notice that there is a column named 'id' in Customer and a column named 'customer_id' in Order. So I ask the tool to discover a relationship between id and customer_id and it tells me that there is a one-one or one-many or no relationship by comparing values. I heard ERWin would be able to do that but thats expensive. Please do let me know asap.
Problem is, without any relational integrity and constraints designed into the database, it probably already contains a lot of data that violates the logical relationships. That makes it impossible for any tool to definitively say what the relationships should be based solely upon the existing data.
I have a script that finds natural keys within a table, which you can use to set the primary key, but that's about it.
Chances are, 10% of your time is going to be occupied with finding out what the relationships are supposed to be, while 90% will involve fixing the bad data you find.
And this: "Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables" is total bull. They are just bad developers. I can set a constraint or a foreign key in 30 seconds. They just didn't want to be bothered taking the time to make sure their code submitted correct data to the database, and so they allowed the database to accept any old crap that is sent to it. That's why you have a mess on your hands.
If it's not practically useful, then it's practically useless.
I used ERWin in order to deduce references. But unfortunately even that didn't suggest much. ERWin tries to deduce what would be relationships between tables. I guess now I have to use logic in order to figure out what the relationships would be.