0

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.

1

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)

  • As suggested by @Akina I decide to go for Many to Many Relationship and I am going to do it like linked_from=D, linked_to=G and linked_from=G, linked_to=H. I will do Self Join to join table_item to itself. So I am not sure how will I fetch other linked items. Do I need to search for id in linked_from first and then linked_to ? What if I came with two similar ids (duplicate) how will I overcome this? Can you plz edit answer and wrote query so that I can accept it. Thanks for all the help. – fwd82 Jun 7 at 19:16
  • @fwd82 - Don't do many:many if it is only 1:many. – Rick James Jun 7 at 20:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.