May 15, 2013 at 11:34 am
I am struggling to find the relationships between tables. There are no foreign key relationships on the tables and I need to work on the ERD project. What's the approach I need to take? I also don't know the table usage. Any suggestions?
May 15, 2013 at 2:02 pm
That's always a tough assignment. Can you interact with the live database? If so, start inspecting the queries in the plan cache to see how data clients are interacting with the data. Look for tables that are part of other table's names, e.g. Person and PersonAddress are likely related. Look for columns with the same name in different tables, e.g. PersonID in Person and PersonAddress.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 15, 2013 at 5:36 pm
Does the database have stored procedures that contain key relationship logic for insert, update and delete operations?
May 16, 2013 at 9:01 am
Database contains stored procs. So you want me to inspect all those stored procs and find the logic. We have php scripts running at the frond end which fill the data into these tables and use the stored procs just to manipulate or make changes mostly. So this means i have to attach the php scripts and find the logic from there??? It sounds damn hard to me.
May 16, 2013 at 9:07 am
Some application designers make the decision to put the referential integrity in the stored procedures instead of in the data model.
If that is the case, you will hopefully find the pattern used to do that in the stored procs. You can then search through syscomments or sys.all_sql_modules and sys.sql_modules for strings that fit the pattern.
May 16, 2013 at 9:13 am
This is very hard. There is no magic way for SQL Server to understand how any tables are related without constraints in place to enforce DRI. If the application developers intended to handle all this in their code (T-SQL and/or stored procs), you have no way of knowing what the relationships are.
I would start documenting them as you find relationships based on queries, code, or how the data appears to the users. That's the best way you can do things.
You can start to build your own DRI in, but you have to ensure that the data matches up correctly (no orphanded children), and ensure the front end has error handling logic to catch things like duplicate keys or problem deletes.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply