I have realized a solution for a similar problem, but since I am more a power user than a database professional I can't tell if this is really a good solution. I can only tell that it works nice for our company (some 50 concurrent users, documents usually consist of some 25 Headings with some 1 - 15 sub paragraphs).
>From scratch, basically I do have 3 tables: T_Index: - IDParagraph (BigInt) - IDParent (BigInt) // Uplink to parent paragraph, NULL if on 1st level - IDDocument (BigInt) // The "document" as such - IDType (SmallInt) // e.g. Heading, sub heading, title... - IDStatus (SmallInt) // e.g. confirmed, pending... T_Content - IDParagraph (BigInt) - TXParagraph (BLOB Type 1/80) T_HyperLinks - IDHyperlink (BigInt) // For further annotations in other tables... - IDParagraph (BigInt) // Source paragraph for a hyper link - IDTarget (BigInt) // Target paragraph for a hyper link I do - of course - have more tables in the system. For example I have comments on the hyperlinks, qualifications of hyperlinks, modification protocol (who changed what and when...), some tables for results of parsing (to find keywords in a range of documents) etc. Also you can have keywords pointing to documents, documents to documents, paragraphs to documents... Processing the hierarchy within one document is very easy using a recursion. I usually use a TreeView component to display to the user. I hope I did not misunderstand what your question was :-) best regards, André > This is probably a question for Stack Overflow, but I would like a > Firebird specific answer so asking here. > I would like to store simple documents in a Firebird database as an > ordered collection of paragraphs as follows: > Document 1 Record 1 ->> 1. Heading 1 Record 2 ->> 1.1 Heading 2 Record 3 ->> 1.1.1 Heading 3 Record 4 ->> 1.1.1.1 Paragraph bla bla bla Record 5 ->> 1.1.1.2 Paragraph bla bla bla Record 6 ->> 1.1.2 Heading 3 Record 7 ->> 1.1.2.1 Paragraph bla bla bla Record 8 ->> 1.1.2.2 Paragraph bla bla bla Record 9 ->> 2. Heading 1 Record 10 ->> 2.1 Heading 2 > ... > Document 2 Record n ->> 1. Heading 1 Record n + 1 ->> 1.1 Heading 2 > and so on. > I would like concurrent access to different paragraphs of the > document and each record can link to paragraph(s) (records) in > another 'document'. Therefore I want to move away from creating the > document in XML and storing it in a BLOB. > I think CTE is out because I can't determine the order of siblings > and nested sets are not good for doing a lot of inserts (as is likely when > writing documents). > Probably the obvious way is to use path enumeration, but I guess > this is a relatively common problem (although googling doesn't find > many results) and wondered if anyone has a good solution? > ------------------------------------ > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402