November 5, 2008 at 12:08 am
Hiya all,
Here's the situation. I have a database with over 100 tables. No primary / foreign key relationship set up and no database diagram. I need to extract data but finding which keys relate to which is a matter of looking at a field name, and then going through all the table trying to find what it links to.
I tried first using Visio, but it yielded no results. So checked the database and then realised why, no primary or foreigh keys set up. So has anyone got any idea on how to link the fields in this situtation? I assume a application is needed that would read field names and then try link it in the other tables?
Please help...having very little luck here 🙁
PS: No..was not me that designed the DB 😉
November 5, 2008 at 3:27 am
crazyfig (11/5/2008)
Hiya all,Here's the situation. I have a database with over 100 tables. No primary / foreign key relationship set up and no database diagram. I need to extract data but finding which keys relate to which is a matter of looking at a field name, and then going through all the table trying to find what it links to.
I tried first using Visio, but it yielded no results. So checked the database and then realised why, no primary or foreigh keys set up. So has anyone got any idea on how to link the fields in this situtation? I assume a application is needed that would read field names and then try link it in the other tables?
Please help...having very little luck here 🙁
PS: No..was not me that designed the DB 😉
For me the above information is not complete but still::
In such case, first select the required tables to work on, and then try to add an identity column based on any unique field say DateCreated etc. or you can also put direct join on the unique field in the table with other tables.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 5, 2008 at 3:40 am
Hi,
Thanks for the feedback, but thats part of the issue Im having in a way. I would have to go through each table and field name, to link them. Is there no software lurking about that checks field names in tables and links them according to field names?
November 5, 2008 at 3:45 am
crazyfig (11/5/2008)
Hi,Thanks for the feedback, but thats part of the issue Im having in a way. I would have to go through each table and field name, to link them. Is there no software lurking about that checks field names in tables and links them according to field names?
I am not sure if there is any software that can do this, but i don't think it would be too accurate or wise to link tables based up field names. Unless you have really good naming conventions then it could turn into a mess.
It may be better to go throough the procedures and applications that use this database to see if you can work out the correct relationships from them, then add primary keys to your tables.
November 5, 2008 at 5:54 am
Matching Column Name A in Table 1 to Column B in Table 2 is not really that difficult BUT
as the previous poster mentioned that gives no indication as to whether the Column is on the primary or foreign key side of the relationship. You will have to work through the DB to figure this out and ad unique or primary key contraints to tables and then start to build up the relationships.
Start with "Obvious" Tables that should represent Master data and Heirarchies
November 5, 2008 at 6:22 am
if you are lucky, you can hope for a couple of design standards that could make it easier.
IF you can assume that FK columns have the same name as it's Reference, and also that any PK columns are the First column in the "Parent" table, this sql might help: it does identify 95% of my relationships in my database,
SELECT
PKS.TABLE_NAME,
PKS.COLUMN_NAME,
PKS.ORDINAL_POSITION,
FKS.TABLE_NAME AS FKTABLE_NAME,
FKS.COLUMN_NAME AS FKCOLUMN_NAME,
FKS.ORDINAL_POSITION AS FKORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS PKS
INNER JOIN INFORMATION_SCHEMA.COLUMNS FKS
ON PKS.COLUMN_NAME = FKS.COLUMN_NAME
AND PKS.TABLE_NAME <> FKS.TABLE_NAME
AND PKS.ORDINAL_POSITION = 1
AND FKS.ORDINAL_POSITION <> 1
Lowell
November 6, 2008 at 9:54 am
Another thing to be aware of - are you sure that all the applications that use this db were written with declarative RI in mind? Because if not, you may find that when you add your relationships, a lot of stuff breaks . . .
November 6, 2008 at 10:12 am
Is there no one around that has worked on the tables and maybe has a clue?
Is the application written in house and the people that wrote it available for information?
Is this a third party application where there is documentation on the tables or maybe they don't want you to be looking at the table structure? Personally, I would never recommend buying a third party application that didn't want you to look at their tables.
If there is no other information around, don't take short cuts but dig in and find out what it is. And, obviously, don't be changing the structure on a production machine without rigorous testing.
Steve
November 6, 2008 at 12:10 pm
There's a number of third party design tools which'll reverse engineer your database into a data model and help you start organizing and documenting the design. The problem space is pretty well defined now, having been studied and solutions brought forth for over a decade.
ERStudio is an example of such a tool. However, they don't necessarily come cheap. There other tools, but I must confess I haven't done a review of this space for the last few years. I'd checkout the current review and see how they stack up price vs performance.
Remember, even still depending on the specifics of the tables and column names, you'll probably just end up with a "best guess". You'd still want to verify each and every relationship and even then, a large dose of good judgement.
NB: If your manage balks at the cost, consider how long you'll struggle with this problem and if you'll need to do it again. Often, the payback is only one or two projects.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply