0

Say for example that I have a table of items like so:

  ID
------
  0
  1
  2
  3
  4

and each item in the table needs to be able to 'link' to N number of other items in that same table. So for example item 0 might link to item 2 and item 4. The first obvious solution seems to be to store some sort of array as another column, and that array can contain integers to 'link' to the other items in that same table. However SQL doesn't really have an array feature, and using a string will make doing specific queries take longer because I'll have to use LIKE %%.

So there must be some way to do it using another table to store the dynamic list of integers.

What's the normal way to store this sort of data in SQL?

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
Ryan Weinstein
  • 7,015
  • 4
  • 17
  • 23

1 Answers1

1

It seems like you're trying to link database items via a many-to-many relationship.

To map this in a relational SQL database, you would have one table containing your nodes/items, where you can describe their "name" and any other attributes they have:

item_id | name
----------------
0       | test
1       | abcd
2       | node_2
3       | item_3
4       | item_4

And then you would have another table mapping elements (0->2, 0->4) together, which describes a connection between two nodes in the graph, as the following connection table:

connection_id | item_id_1 | item_id_2
--------------------------------------
0             | 0         | 2
1             | 0         | 4

Each connection has it's own id for easy editing, and it links to the items via their ids. You can specify as many connections between nodes as you like in this table. This maps a one-way, many-to-many relationship. You can use a similar table to map two different entities together, by just mapping the ids.

As an example, you can then run a query to get the names of two joined items like so:

SELECT
    i1.name AS 'item_1_name'
    i2.name AS 'item_2_name'
FROM
    connection AS c
    INNER JOIN items AS i1
        ON i1.item_id = c.item_id_1
    INNER JOIN items AS i2
        ON i2.item_id = c.item_id_2

Which would return the following:

item_1_name | item_2_name
-------------------------
test        | node_2
test        | item_4

For more detailed info on setting up the tables, there is this stackoverflow question: How to make SQL many-to-many same-type relationship table although his answer about selecting them is vulnerable to SQL injection, so I'd be very careful with the PHP variables he mentions.

Community
  • 1
  • 1
Liam Gray
  • 1,089
  • 9
  • 16
  • But the items are dynamic. Surely I can't have 100k columns in a table? – Ryan Weinstein Aug 26 '15 at 18:43
  • data is stored in rows, not columns. – Bulat Aug 26 '15 at 18:53
  • You only add rows to the connection table which links nodes together. Your nodes can then have a name, date and a bunch of other columns to describe their data (typically 1-20 extra columns). This may help you to understand relational tables: http://www.htmlgoodies.com/primers/database/article.php/3478051 – Liam Gray Aug 26 '15 at 18:54
  • 1
    I used this answer to design my solution and its working great. I have over 10 million unique connections in my database now. I also had a bit of an issue optimizing queries so I created an index of item_1 and item_2. I'm just posting this comment in case anyone else has a similar optimization issue. – Ryan Weinstein Aug 28 '15 at 22:51