SQL table items, self referring to each other

Community forum for discussions completely unrelated to MediaMonkey.

モデレータ: Gurus

Teknojnky
記事: 5537
登録日時: 火 9 06, 2005 11:01 pm

SQL table items, self referring to each other

投稿記事 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

コード: 全て選択

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?

コード: 全て選択

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
記事: 10023
登録日時: 金 8 26, 2005 3:28 am

Re: SQL table items, self referring to each other

投稿記事 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
記事: 5537
登録日時: 火 9 06, 2005 11:01 pm

Re: SQL table items, self referring to each other

投稿記事 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.

コード: 全て選択

_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
記事: 3450
登録日時: 土 11 05, 2005 7:17 pm

Re: SQL table items, self referring to each other

投稿記事 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):

コード: 全て選択

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

コード: 全て選択

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
記事: 5537
登録日時: 火 9 06, 2005 11:01 pm

Re: SQL table items, self referring to each other

投稿記事 by Teknojnky »

コード: 全て選択

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


画像
返信する