Circular Relationships

I am occasionally reviewing database designs and one of the things I like to check is normalisation. One indication that a table structure isn't in third normal form is the existence of circular relationships. That is where you can navigate from one entity back to itself via circular relationships. I should stress that the existance of a circular relationship does not necessarily mean that we aren't in 3rd normal form, but it is a good clue.

Here is an example in the picture of a circular relationship. From the Products table you can get to ProductType directly or via ProductGroup.

To search for circular relationships that are declared by referential constraints, you can use the following SQL query. (Note, it is only looking for a three level relationship, and it could be enhanced to find deeper redundant relationships.)

select
OBJECT_NAME(fk.parent_object_id) Child
,OBJECT_NAME(fk.referenced_object_id) Parent
,OBJECT_NAME(fk1.referenced_object_id) Intermedidate
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.parent_object_id and c.column_id=fkc.parent_column_id
where fkc.constraint_object_id=fk.object_id) FK_FirstColumn_FirstRelationship
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.referenced_object_id and c.column_id=fkc.referenced_column_id
where fkc.constraint_object_id=fk.object_id) PK_FirstColumn_FirstRelationship
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.parent_object_id and c.column_id=fkc.parent_column_id
where fkc.constraint_object_id=fk1.object_id) FK_FirstColumn_IntermediateRelationship1
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.referenced_object_id and c.column_id=fkc.referenced_column_id
where fkc.constraint_object_id=fk1.object_id) PK_FirstColumn_IntermediateRelationship1
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.parent_object_id and c.column_id=fkc.parent_column_id
where fkc.constraint_object_id=fk2.object_id) FK_FirstColumn_IntermediateRelationship2
,(select top 1 name from sys.foreign_key_columns fkc
inner join sys.columns c on c.object_id=fkc.referenced_object_id and c.column_id=fkc.referenced_column_id
where fkc.constraint_object_id=fk2.object_id) PK_FirstColumn_IntermediateRelationship2
from sys.foreign_keys fk
inner join sys.foreign_keys fk1 on fk1.parent_object_id=fk.parent_object_id
inner join sys.foreign_keys fk2 on fk2.parent_object_id=fk1.referenced_object_id
and fk2.referenced_object_id=fk.referenced_object_id

Remember, there is a lot of information in the relational catalog. We should use when we want need information about our data structures.