SQL table items, self referring to each other

Community forum for discussions completely unrelated to MediaMonkey.

Moderator: Gurus

Teknojnky
Posts: 5537
Joined: Tue Sep 06, 2005 11:01 pm
Contact:

SQL table items, self referring to each other

Post 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.
trixmoto
Posts: 10024
Joined: Fri Aug 26, 2005 3:28 am
Location: Hull, UK
Contact:

Re: SQL table items, self referring to each other

Post 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.
Download my scripts at my own MediaMonkey fansite.
All the code for my website and scripts is safely backed up immediately and for free using Dropbox.
Teknojnky
Posts: 5537
Joined: Tue Sep 06, 2005 11:01 pm
Contact:

Re: SQL table items, self referring to each other

Post 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.
Steegy
Posts: 3452
Joined: Sat Nov 05, 2005 7:17 pm
Contact:

Re: SQL table items, self referring to each other

Post 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.
Extensions: ExternalTools, ExtractFields, SongPreviewer, LinkedTracks, CleanImport, and some other scripts (Need Help with Addons > List of All Scripts).
Teknojnky
Posts: 5537
Joined: Tue Sep 06, 2005 11:01 pm
Contact:

Re: SQL table items, self referring to each other

Post 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
Post Reply