Mysql – One column link to others

database-designMySQL

I always search my problem with different search terms but the problem I am facing now I can't express this in search keyword. So instead I came here to ask directly from humans as machines were unable to what I exactly means.

So coming to scenario:

I have database with having table_item and table_item_linked. table_item has many items. User come and add item(s). Later other user came and link one item with different item via a form with two dropdown.

What I did so far is: 

Structure of `table_item`:
- item_id (Primary) 
- others..

.

Structure of `table_item_linked`:
- linked_id (Primary) 
- item_id (Foreign key referencing -> item_id of table_item)
- linked_items (here I need to store ids of linked items)
- linked_by (referencing to user_id of user_table)
- linked_timestamp (timestamp)

If I have items like:
A B C D E F G H

When I link D with G

I can successfully fetch G when I am fetching D or vice versa. But problem came when I

Link H with G

So I must fetch D H while fetching G.

It is like a multiple relation.

Guys I know there must be professional way to do it. I will like to have any guidance. I can even change my database structure.

PS:
Please don't suggest to add #tag as one item is exactly similar to the other linked.

Best Answer

Decide which you type of "relationship" each "link" is: "1:1", "1:many", or "many:many".

1:1 should be represented by having the columns in the same table. (There are exceptions, but you aren't there yet.

1:many is implemented via an id of the "1" in the "many" table. Example: A person owns "many" cars. The cars table has a column person_id to say who it is owned by.

many:many needs an extra table with ids of both of the tables. Example: students and classes:

CREATE TABLE WhoTakesWhat (
     student_id INT UNSIGNED NOT NULL,
     class_id INT UNSIGNED NOT NULL,
     PRIMARY KEY(student_id, class_id),
     INDEX(class_id, student_id)
) ENGINE=InnoDB

I encourage you to be thinking in terms of SQL; you will eventually have to use it.

Meanwhile:

A "database" contains several "tables".
A "table" is a set of "rows" and "columns".
"Columns" tend to be dissimilar (id, name, date, ...)
"Rows" are different instances of what is being stored in the table (various "students" in the table named students)

Related Question