Identifying/Mapping relations w/in tables? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Identifying/Mapping relations w/in tables?

Hello All,<br /><br />I have a Database with around 25 to 30 tables, my problem is that the relationships aren’t very apparent. Example, I have a table called "PhoneOwner" that contains as a PK, "acct_num" also a non-pk field called "charge_id". I have a second table called "IDNames" which contains 2 fields: "ID" (&lt;– PK) and "Names" (non-PK). Between the two no obvious relation exists, so like…<br /><br /><b>Tables</b>: <u>PhoneOwner</u>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<u>IDNames</u><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />K-&gt;acct_num&nbsp;&nbsp;&nbsp;&nbsp;&nbsp<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />K-&gt;ID <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;charge_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Names<br /><br />However, after massive dabbling (back-and-fourth from application to tables) I’ve discovered the connection between "PhoneOwner" and "IDNames". Basically, from an input box a user types a name which sets IDNames.Names which automatically increments IDNames.ID then PhoneOwner.charge_id is then set to IDNames.ID.<br /><br />My question, is there any way to make tracking these relationships less mind boggling? If I set up a trace in SQL Profiler will this show me exactly which values are being set to what when dabbling w/ the application?<br /><br />Thanks for any and all help!<br />
if the relationships are not declared within SQL and there is no data model, then working your way through the application (with the help of sql profiler perhaps) is the only way I think… Cheers
You can also create a new database diagram for these tables in order to get the clear picture for the relationship. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thank you both for your responses! Satya, the issue regarding data diagram is that obvious relationships (pk, fk, etc) don’t exist between all tables. For example, the two tables I mentioned in my first post have no direct/depicted (in the case of diagrams) relation, yet PhoneOwner.charge_id is always set to IDNames.ID. So unfortunately diagrams don’t supply the exact info I need. Twan, I was afraid profiler was my only hope. Doh!! No data models exist to boot. Thanks again, if any other ideas surface I’d love to hear about’em.
Also check indices for hints about commonly joined fields. Though I suspect if theres no docs the db is a bit amateur and so indices will be a mess too. Using profiler shouldnt be too difficult, your main area of focus should be JOIN criteria I guess. I wouldnt use MS diagramming tool. Its poor and also will create constraints whenever you link tables. This is ideal when youre designing an empty schema but can cause all sorts of problems for a system that wasnt written with that in mind. Maybe erwin or visio might be helpful — be careful because visio also has options to modify schema based on the diagram.
Thank you Chappy, and you are correct — all db docs are way out of date. It appears that all signs are directing me towards Profiler. Fun stuff, looks like my weekends shot. Thanks again everyone!