Page 1 of 1

SQL table items, self referring to each other

Posted: Tue Aug 16, 2011 2:11 am
by Teknojnky
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: Select all

0 item1
1 item2
2 item3
3 item4
4 item5
5 item6
6 item7
7 item8
8 item9
9 item10
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: Select all

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

Re: SQL table items, self referring to each other

Posted: Tue Aug 16, 2011 2:58 am
by trixmoto
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.

Re: SQL table items, self referring to each other

Posted: Tue Aug 16, 2011 3:05 am
by Teknojnky
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: Select all

_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
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.

Re: SQL table items, self referring to each other

Posted: Tue Aug 16, 2011 3:21 pm
by Steegy
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 ArtistSongs, ArtistsAlbums, ... (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: ID, Desc, ...
Table2: ID, Desc, ...
Ref: ID, ID1, ID2, ... (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 all

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'
If you change (only) Table2 to Table1, 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: Select all

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

Re: SQL table items, self referring to each other

Posted: Tue Aug 16, 2011 3:50 pm
by Teknojnky

Code: Select all

_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
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).


Image