CODEWITHSUNDEEP
sql-server
Docu
Docu

Reputation: 147

Best way to do many to many relation on the same table

I want to implement a new function in my app in order to link a file with other files (many to many relation). I thought that the best solution is create a "RelationTable" that contains duplicate fileId pairs. I mean:

RelationTable

FileX FileY

file1 file2
file2 file1
file3 file4
file4 file3
file5 file2
file2 file5

To see all files linked to "File2" I would make this query:

select * from RelationTable
where FileX = file2

My question is, this is the best solution? are there other ways to improve it?

Thanks in advance

Upvotes: 1

Views: 70

Answers (2)

Quassnoi
Quassnoi

Reputation: 425371

Sure you can use your solution, just index the table properly:

CREATE UNIQUE CLUSTERED INDEX -- or ALTER TABLE ADD PRIMARY KEY CLUSTERED
        ux_relationTable_X_Y
ON      relationTable (fileX, fileY);

CREATE INDEX
        ix_relationTable_Y
ON      relationTable (fileY);

Update:

As you are using SQL Server, you can run accross edge cases with trailing blanks, which are trimmed on insert but can be meaningful in filenames.

Update 2:

If your mappings are always bidirectional, do not create the second index (on fileY), it's redundant.

Upvotes: 2

ben75
ben75

Reputation: 28706

An alternative is using a CASE and then you don't need to have two rows for each biderectionnal relationship:

select file2 as X,
       (CASE WHEN fileX = file2 
           THEN fileY 
           ELSE fileX
        END) as Y 
from RelationTable 
where (fileX=file2 or fileY=file2);

to fetch all files related to file2

On a table like this

fileX    fileY
--------------
file2     file1
file3     file2

The result will be

X        Y
---------------
file2    file1
file2    file3

Upvotes: 0

Related Questions