Subject Re: [firebird-support] Storing headings and paragraphs in a Firebird database
Author André Knappstein
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:
- IDParagraph (BigInt)
- IDParent (BigInt) // Uplink to parent paragraph, NULL if on 1st
- IDDocument (BigInt) // The "document" as such
- IDType (SmallInt) // e.g. Heading, sub heading, title...
- IDStatus (SmallInt) // e.g. confirmed, pending...

- IDParagraph (BigInt)
- TXParagraph (BLOB Type 1/80)

- 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,

> 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 ->> Paragraph bla bla bla
Record 5 ->> Paragraph bla bla bla
Record 6 ->> 1.1.2 Heading 3
Record 7 ->> Paragraph bla bla bla
Record 8 ->> 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 and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !

> Also search the knowledgebases at

> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> 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: knappstein@...

Amtsgericht Hamm Nr. B 420
Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus

USt-IDNr.: DE 125215402