Problem connecting two instances of the same RDB table via an intermediary table

Question

I have also asked this question on Stack Overflow (apologies if this is a breach of protocol).

I am migrating an MS Access database to MS SQL Server where I will maintain it using SQL Server Management Studio (SSMS).

The Access db has a relationship that connects two instances of the same table via an intermediary table as indicated by the following access relationship diagram.

This entity relationship enables the representation of the organization structure.

Access lets you create the relationship above using drag and drop and automatically added the suffix _1 to distinguish the added instance.

The SQL is:

SELECT [T-Organisation].[Organisation Unit Id], 
[T-Organisation].[Organisation UnitName], 
[T-Organisation_1].[Organisation Unit Id], 
[T-Organisation_1].[Organisation UnitName] 
FROM ([T-Organisation] 
INNER JOIN [T-OrganisationSubsidiary] ON 
[T-Organisation].[Organisation Unit Id] = [T-OrganisationSubsidiary].[Parent Organisation Unit]) 
INNER JOIN [T-Organisation] AS [T-Organisation_1] ON 
[T-OrganisationSubsidiary].[Subsidiary Organisation Unit] = [T-Organisation_1].[Organisation Unit Id];

Unless I am missing something SSMS doesn’t permit me to add (drag and drop) copies of tables to the Relationship Diagram like Access does. Does this mean I need to create a physical copy of the T-Organisation table and manually name it e.g., T-Organisation_1 or is there a better solution?

Answer 1

Unless I am missing something SSMS doesn’t permit me to add (drag and drop) copies of tables to the Relationship Diagram like Access does.

If by Relationship Diagram, you mean the Database Diagram feature in SSMS, then correct, you can't add the same table twice. This is true in a logical Entity Relationship Diagram too, one does not add the same table twice to such a diagram. Microsoft Access is unique in this regard, to help write the SQL for you.

Does this mean I need to create a physical copy of the T-Organisation table and manually name it e.g., T-Organisation_1...

I mean, you could do that, but it would be a silly thing to do, just to get the visual to work. This would cause you to have to maintain the same data in two tables, which is an anti-pattern / bad design. Most people don't even use the Database Diagram feature.

...or is there a better solution?

Don't use the Database Diagram feature, and don't create a copy of the table. Create a View to store the query that relates the table to itself. This is essentially the query you've provided from Microsoft Access where T-Organisation inner joins to T-OrganisationSubsidiary inner joins to T-Organisation again.

---

I have also asked this question on Stack Overflow (apologies if this is a breach of protocol).

For future reference, that's called double posting, and is typically not allowed. You should probably close the instance of the question on StackOverflow. Database questions are usually best suited for here (DBA.StackExchange) where more programming oriented questions go on StackOverflow.