I think you should limit yourself to one table, at least for the purpose of
parent<->child relationships.
I am working on a similar problem right now. (I started with sqlite, but moved
to mysql for reasons outside
of this problem scope.) My solution, which performs fine, is as follows:
I put all objects into a single table. The parent_id attribute is used to
associate the parent of an entry. Now a query like that
SELECT a.id AS content_id, a.parent_id AS ancestor_id FROM contents a
WHERE a.parent_id IS NOT NULL
UNION
SELECT a.id AS content_id, b.parent_id AS ancestor_id FROM contents a
INNER JOIN contents b ON a.parent_id=b.id
WHERE b.parent_id IS NOT NULL
UNION
SELECT a.id AS content_id, c.parent_id AS ancestor_id FROM contents a
INNER JOIN contents b ON a.parent_id=b.id
INNER JOIN contents c ON b.parent_id=c.id
WHERE c.parent_id IS NOT NULL
selects content_is, ancestor_id pairs up to 4 levels deep. This performs fine
with a dataset of 50000 elements, and an average depth of 3..4
I tried this once with 5 levels and it still felt ok.
Even faster than that was a query like this
SELECT a.id AS aid, a.parent_id AS bid, b.parent_id AS cid, d.parent_id AS did
FROM contents a
LEFT JOIN contents b ON a.parent_id=b.id
LEFT JOIN contents c ON b.parent_id=c.id
which obviously might contain NULL entries (e.g. 1, 2, NULL, NULL) in case
there are no 4 levels.
Another approach would be to maintain a relation table holding all those
entries which is updated using triggers.
May be this is a direction you could go, too?
/eno
> I have 5 tables that store records for 5 different types of documents.
> Each type of document can be a descendent of a document in one of the
> other tables.
>
> For example, let's call the tables A, B, C, D and E.
>
> If you create a record in A, and then create a record in B, you can
> associate the document in B with the record in A. You could then do
> the same with a record in C, D, and E.
>
> One wrinkle is that you can skip documents, so it's not always a
> direct chain. For example, you could have A > B > E.
>
> Another wrinkle is that you don't have to start at A, so you could
> have B > C > D > E. You can actually start at any point in the cycle.
>
> Another wrinkle is that you can loop back around to other tables
> again, so something like A > B > E > A (not the same record as the
> first A) > C > E is possible. Of course, you can't link back to a
> record that is already in the chain though, so as to avoid and
> infinite loop.
>
> A final wrinkle is that each parent record can have more than one
> linked child in other tables.
>
> Each table has 2 columns: ParentTable and ParentRecordID that hold the
> associated record from the immediate parent table for each child
> record (or null for records that are at the top of a chain, or
> completely unchained at this point).
>
> Now Table A is kind of special in that I want to be able to figure out
> the deepest descendents of all of the records in Table A in any other
> tables (if any) and display data from the deepest linked records
> (using group_concat on a fields in those linked records).
>
> So, for example, if we had record chains such as:
> D1 < B1 < A1
> D2 < C1 < A3
> E1 < B3 < A1
> E2 < C2 < A1
> C3 < B2
>
> I want to be able to show a cell for each record in Table A like:
>
> TABLE A
> A.ID A.Title DeepestDocuments
> ------------------------------------------
> 1 Doc1 E.Doc1, E.Doc2
> 2 Doc2 Null
> 3 Doc3 D.Doc2
>
> You'll notice that even though A1 has descendents in tables B, C and
> D, only descendents in Table E are listed in the DeepestDocuments cell
> because that is the deepest table with descendents.
>
> I've got what I need to work by manually looping through records and
> running up the chain to see when I get a null parent record to
> determine the top level reference, and then concatenating all of the
> deepest records fields as required, but it is quite slow. On 100 rows
> it can take 3-6 seconds on a reasonably fast computer, but table A
> could conceivably contain a few thousand records, which would make it
> far too slow for my needs.
>
> I'm wondering if anyone has any ideas on how I might accomplish what I
> need using SQLite supported SQL. I suspect I need to do something with
> UNION statements, but my experience with them is non-existent, and
> nothing I've tried yet has worked.
>
> Thanks in advance for any help in this matter.
> Jason
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
