I am working on an application that is using lots of self referencing keys in the SQL Server database. I want to find out if it is a good idea to use these self referencing keys and if not what is the best approach
Answer by Magnus Ahlkvist ·
This is very much an "it depends" case. With self referencing keys, you can create a hierarchy which is sometimes necessary. Querying the data will sometimes be a little more complex. If you are always only after one level (find parent or find children), it's no more than a "normal" join. If you are looking for more complex scenarios (like find all children and their children and their children etc), you're looking at recursive common table expressions, which don't perform awfully good. An alternative to that COULD be to use the hierarchyID data type in SQL Server. However, that data type makes inserts to the table much less performant.
In more recent versions of SQL Server, you have support for Graphs with nodes and edges. Nodes in this case will be your entities (user, usergroup) and edges will be the relation between nodes. With graphs, you'll get additional T-SQL commands and clauses to traverse graphs. With that said, the graph support in SQL Server 2017 is extremely limited and in SQL Server 2019 it's a little less limited but still very, very far from the support you have in specialised graph databases. For your purpose with users and user groups, you most probably have enough support in SQL Server 2019.