I am currently making a knowledge base type document store, the problem I am encountering is that I want to associate documents to one another and also display them according to their association. Currently, I am storing the document data in one table and the associations in another. So :
And my sql to pull back the associations :
see code snipet.
This works currently but if I want to have any more than 3 levels of association (Parent, Child1, Child of Child1, Child of Child 2) I have to add another column to my DocumentParen table which is inflexible really. Also, I am not sure how well it handles a document that is associated with more than one document. Do I need to look at a cursor with a recursive loop?
Sorry for the poor question title, I was unable to think of a good title!
Select DocumentiD, DocumentTitle
tblDocument LEFT OUTER JOIN
tblDocumentLinkiD ON tblDocument.DocumentiD = tblDocumentLinkiD.DocumentiD
DocumentParentiD, DocumentParenitLevel2iD, DocumentParentLevel3iD, DocumentParentLevel4iD