ERD drawings for the tables not related

  • 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?

  • 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

  • Does the database have stored procedures that contain key relationship logic for insert, update and delete operations?

  • 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.

  • 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.

  • 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