For a small data set where you have a child document to a parent record and you have a child document to a potential child document, self joining seems like a low effort to accomplish:
| PK | Document | Document Parent PK | ... | | PK | Document A | "" | ... | | PK | Document B | Document A | ... | | PK | Document C | Document A | ... | | PK | Document D | Document C| ... | If you DO NOT have a "Document Parent PK" then that is your top level record. If you DO have a "Document Parent PK", the record is a child document. You could make it a little bit easier if you put on a type | Document Type |, which could reduce the logic needed to determine if a child has a child. There are recursive self joining query examples on the web, but in your case you know there is a finite number of recursive possibilities, which seems like two "deep." There is a Celko model that fits this type of approach, but I am not sure you need that level of engineering. Teddy R. Payne, ACCFD Google Talk - teddyrpa...@gmail.com On Mon, Feb 22, 2010 at 3:04 PM, Donna Martin <dmar...@gmail.com> wrote: > No. As in the case of an addendum or appendix, it relates to ONLY one > document. However, that appendix/addendum or whatever type is also a > document with the same tracking requirements. However, an addendum can have > another document related to it, as in the case of an Amendment to that > addendum...Rare, but there. > > Thx > Donna > > On Mon, Feb 22, 2010 at 2:31 PM, Teddy R. Payne <teddyrpa...@gmail.com>wrote: > >> Donna, >> Will any of the "non primary" documents be associated with more than one >> primary document? >> >> >> Teddy R. Payne, ACCFD >> Google Talk - teddyrpa...@gmail.com >> >> >> >> >> On Mon, Feb 22, 2010 at 2:09 PM, Donna Martin <dmar...@gmail.com> wrote: >> >>> Good afternoon, >>> >>> Normally, I would not hesitate to create a self-join on an Oracle >>> table. However, in this instance, people from Africa are going to be using >>> the ColdFusion/Oracle site that I am creating on a very regular >>> basis. Their bandwidth is abominable. >>> >>> The plan is to sort documents based on document type and its association >>> with another documents. As an example, I will be listing a procedure (SOP), >>> and if record exists, all addenda, appendices, etc (DocumentTypes) -- hence, >>> the self-join. >>> >>> The table will not be huge-- between 2 - 3 thousand records. Is the >>> performance hit going to be great (Think 'very low' bandwidth here)? >>> >>> To make matters worse, there are also 3 types of training documents >>> that can be associated with the same primary document (the procedure in the >>> above example). At this point, I have opted to put these into another table >>> with an FK to avoid using more self-joins. >>> >>> Is this the best way to go? Let me know if you need more details. I'm >>> at a standstill until I get this resolved... >>> >>> Thanks much. >>> Donna >>> >>> PS It's been a long time since I have posted to this group...miss the >>> interaction! >>> >> >> >
