_id   pid   cid position#
0   0   1   1
1   0   2   2
2   0   3   3
3   1   0   2
4   1   2   3
5   1   3   1
6   2   1   3
7   2   0   2
8   2   3   1
9   3   2   1
10   3   1  3
11   3   0   2

[code]
_id pid cid position#
0 0 1 1
1 0 2 2
2 0 3 3
3 1 0 2
4 1 2 3
5 1 3 1
6 2 1 3
7 2 0 2
8 2 3 1
9 3 2 1
10 3 1 3
11 3 0 2[/code]

So this looks like what I need, the position column represents the order of the child relative to the root/parent.

I think for now, all I will need to work with is finding an item, then its list of related children.

I won't necessarily be working as child and trying to find its parent or higher/lower siblings or showing any relations between the root/parent items.

visualized as a spreadsheet, the columns and rows would be the same names (ever expanding both horz/vert), with the cells containing the position number (or empty) of that column for each row, where the corresponding intersection of the same row/column name would be position zero (to denote the root for that row).


[img]http://img843.imageshack.us/img843/9154/tablesr.jpg[/img]

SELECT
   T1.Desc,
   T2.Desc
FROM
   Ref AS R
   INNER JOIN Table1 AS T1
      ON R.ID1 = T1.ID
   INNER JOIN Table2 AS T2
      ON R.ID2 = T2.ID
WHERE
   T1.Name = 'Test'

ID | Desc | Refs
0 | item1 | 1,2,3,4,5,6,7,8,9
1 | item2 | 0,2,3,4,
2 | item3 | 5,6,7,8,9
3 | item4 | 0,1,7,8,9
...

Trixmoto's response (same as your last post's example) is the only way to go in a relational database.

MM table examples for these "relationship tables" are [url=http://www.mediamonkey.com/wiki/index.php/ArtistsSongs_table]ArtistSongs[/url], [url=http://www.mediamonkey.com/wiki/index.php/ArtistsAlbums_table]ArtistsAlbums[/url], ... (every time references between records in 2 different tables, but those could also be references to records within 1 single table)

For 1 - 0, 1 - 1 or 1 - multiple relations (which include parent-child relations which can be looped recursively) you only need 2 tables, or in your case 1 single table where the records are refering to other records in the same table.
If you want multi - multi relations, the only way to do that is with adding an extra table that defines each relationship with ID1 and ID2.

Table1: [i]ID[/i], Desc, ...
Table2: [i]ID[/i], Desc, ...
Ref: ID, [i]ID1[/i], [i]ID2[/i], ... (with ID1 refering to Table1.ID and ID2 refering to Table2.ID)

E.g. this would show all relations between Table1 and Table2 (supposing the syntax is correct):
[code]SELECT
T1.Desc,
T2.Desc
FROM
Ref AS R
INNER JOIN Table1 AS T1
ON R.ID1 = T1.ID
INNER JOIN Table2 AS T2
ON R.ID2 = T2.ID
WHERE
T1.Name = 'Test'[/code]If you change (only) [i]Table2[/i] to [i]Table1[/i], then you have records from Table 1 refering to other records in Table1, which is what you want.

In a badly designed database (or if it's the only alternative), you could also work like
[code]
ID | Desc | Refs
0 | item1 | 1,2,3,4,5,6,7,8,9
1 | item2 | 0,2,3,4,
2 | item3 | 5,6,7,8,9
3 | item4 | 0,1,7,8,9
...[/code]but the values in the Refs column then would have to be parsed in your program, outside the database engine, which would make it quite slow.

_id	parentId	childId
0	0	1
1	0	2
2	0	3
3	1	0
4	1	2
5	1	3
6	2	1
7	2	0
8	2	3
9	3	2
10	3	1
11	3	0

Yes but each item can be related to multiple other items (none, some or all)

I was thinking something like the below, but that seems really in-elegant.


[code]_id parentId childId
0 0 1
1 0 2
2 0 3
3 1 0
4 1 2
5 1 3
6 2 1
7 2 0
8 2 3
9 3 2
10 3 1
11 3 0[/code]

It seems like all the examples I can find in google, use the employee/manager recursive example.

However, what I need is something is non-hierarchial. There is no single boss, with a tree of underlings.

There is a forest, and the tree's branches are intertwined in various amounts and positions.

You need two tables...

- Item (ID, Name, etc)
- Relationship (ID1, ID2)

The Relationship table literally just needs to be two indexed fields, Item 1 and Item 2, and both fields are the primary key because you can only relate two items once.

0 item1
1 item2
2 item3
3 item4
4 item5
5 item6
6 item7
7 item8
8 item9
9 item10

0 item1 -> 1,2,3,4,5,6,7,8,9
1 item2 -> 0,2,3,4,
2 item3 -> 5,6,7,8,9
3 item4 -> 0,1,7,8,9
4 item5 -> 1,2,3,5,6,7,8,9
5 item6 -> 8,9
6 item7 -> 
7 item8 -> 2
8 item9 -> 0,1,2,3,4,9
9 item10 -> 0,1,2,3,4,7,8

I'm trying to figure out how best to implement something in a sqlite database...

let's say I have 10 items in a table

[code]0 item1
1 item2
2 item3
3 item4
4 item5
5 item6
6 item7
7 item8
8 item9
9 item10[/code]

each of these items is related to other items in the same list (only once each), but could have none, some, or all.

without creating a separate table for each item, how can I implement the relationship list for each item?

[code]0 item1 -> 1,2,3,4,5,6,7,8,9
1 item2 -> 0,2,3,4,
2 item3 -> 5,6,7,8,9
3 item4 -> 0,1,7,8,9
4 item5 -> 1,2,3,5,6,7,8,9
5 item6 -> 8,9
6 item7 ->
7 item8 -> 2
8 item9 -> 0,1,2,3,4,9
9 item10 -> 0,1,2,3,4,7,8[/code]

Keeping in mind, that the relationship list for each item is/can be completely random, and unrelated to the order of any other item's relationship.

And there is an infinite number of items, so I can't simply keep creating more tables and/or columns.