I’m not too sure if this is the correct forum to post this in, as if a database isn’t the best solution then it becomes a programming question, nevertheless…
I have developed an Inverted Index script using C# to create a structure of Dictionary<string, List<Word>> where the struct Word contains the row ID of the table and its whereabouts within the entry. Within my database I have a table consisting roughly of the following. To make the question easier to follow I’ll claim to have the following within my table taken from this Wikipedia entry:
ID URL CONTENT
------------------------------------------------------------
1 a.com it is what it is
2 b.com what is it
3 c.com it is a banana
So, my inverted index will contain the following:
"a": {(2, 2)}
"banana": {(2, 3)}
"is": {(0, 1), (0, 4), (1, 1), (2, 1)}
"it": {(0, 0), (0, 3), (1, 2), (2, 0)}
"what": {(0, 2), (1, 0)}
All I want to know is how I can store the above within my SQL Server database and whether it is wise to do so. I am writing an information-retrieval system that is queried to return a list of the most relevant rows within the table and whilst I would like to utilise SQL Server for this I have opted not to choose SQL Server Full Text Search because it is not flexible enough to accommodate the extensions that will be required once this is finished.
Can a .NET/SQL Server guru help me out?