Schema diagrams of an RDBMS are something incredibly useful to a data professional as that will show the design of the tables and the relationships between them. Generally drawing them is relatively painful so the best approach is to use Visio Professional and reverse engineer the database into a schema diagram. This takes a few seconds normally.
But what happens when the diagram you get contains several hundred tables which cover multiple domains all intermingled and seemingly no way of unravelling this into something legible ?
Well, this happens a lot, so here is my approach for making sense of the chaos. Be prepared for a lot of work, but hey this is a complex task so hopefully some pointers will make this easy. There is a fair bit of manual processing but hopefully someone can automate this and save more time.
Warning, this is NOT an easy task but it is do-able.
First up, forget Visio Pro for now. We will come back to that later.
My preferred method is to start with RedGate's Dependency Tracker 2.
http://www.red-gate.com/products/sql-development/sql-dependency-tracker/
I would advise you get the full toolbelt if working with SQL as it will save you a lot of time.
Anyway, when you point this at your database you get a different approach to diagramming. Initially this won't look readable, but bear with me. Add all of the tables you want into the project and let it generate the diagram (this will be quick)
You can see several very large circles and it's those you are interested in. This is where the pattern in the chaos starts. The centre of each circle is a table that has a lot of relationships. Make a note of the tables in the centre of the biggest circles. You probably don't have much more than 20 or 30 of these. You will probably find that these are some of the major entities or domains that you would expect in your business (Customers, Orders etc...). Write this list down.
Now, start up a new project in RedGate Dependency Tracker and just pick a single table from your list. When you see that diagram you should ideally have a single circle showing. You now have everything that depends on a major entity (Customers for example).
The pain in the arse is getting that list into something useful. You can get an XML list of the dependencies which is a good place to start.
With a bit of work you can easily get this into a list of tables that directly need the major entity you are working with. This is all you need here. Don't worry about what else relates to everything else. We are trying to break this into a decent slice of the puzzle so we can figure the rest later.
Get yourself a list of all of the tables in the database
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
INFORMATION_SCHEMA.TABLES WITH (NOLOCK)
Drop this into Excel and add a new column for the major entity that you are working with.
Rinse and repeat for each of the major entities so that you have these covered. You will find that the large majority only really relate to a single major entity, however, you will have some that don't. Make a quick assumption here so that each table has a single associated major entity. Don't worry about accuracy too much as this will become clearer later.
Now, back to Visio (and you need the Pro version for this feature). You can now reverse engineer the database into a diagram. Select all of the tables you associated with the major entity (yawn - boring bit).
When you draw your diagram out you then should be able to see something a lot easier to read. If it's only a handful of tables then you can make sense of it.
If you want to take this further, you can then overlay which major entities are relevant to this diagram. To do this, you need to pick the tables off one at a time and work through the foreign keys. Don't add in the other tables, but rather add in a single box that gives the major entity name (Orders for example). You can add the relationships to this 'block' without having to add the detail. You will need to follow the foreign keys for each table and see where they relate to. The script below will show you what foreign keys from a table relate to what.
USE MyDatabase
GO
DECLARE @MyTableName NVARCHAR(100)
SET @MyTableName = 'Customers'
SELECT
C.TABLE_SCHEMA AS Owner_Schema,
C.TABLE_NAME AS Owner_Table,
KCU.COLUMN_NAME AS Owner_Column,
C2.TABLE_SCHEMA AS Related_Schema,
C2.TABLE_NAME AS Related_Table,
KCU2.COLUMN_NAME AS Related_Column,
C.CONSTRAINT_NAME AS FK_Name,
C2.CONSTRAINT_NAME AS PK_Name
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2
ON RC.UNIQUE_CONSTRAINT_SCHEMA
= C2.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME
= C2.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA
AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME
AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND C.TABLE_NAME LIKE @MyTableName
ORDER BY
C2.TABLE_NAME
Once you rinse and repeat for each major entity you will hopefully find you have a readable database schema for the relevant part of the database.
To be honest here, it's a hell of a lot of work, but if you really do need to do this (and I just have) then this approach is worthwhile exploring. Once you have an appreciation of the shape of the data you can then work out the characteristics and then figure out how to re-factor this part of the database etc...
Hope this helps
R
No comments:
Post a Comment