How do I structure a bunch of items that chain together in a MySQL database?

Question 1

Question by HartleySan

I apologize for the vagueness of my question title, but I don’t even know what to call what I’m trying to accomplish.

The best way to describe what I want is that I want to be able to chain a bunch of items together, and then (possibly) recursively find all the items that are part of any chains that contain the target item. For example, note item3 in the following chains:

item1 => item2 => item3 => item4
item5 => item3 => item6  
item3 => item7 => item8  
item3 => item9 => item10  
item11 => item12 => item13 => item3

If a user were to do a search for item3, then I’d want all five chains above to be displayed. In other words, I want to be able to find all descendants and ancestors of item3, so that I can display the data in an HTML table (or whatever HTML structure works best).
The thing that makes this tricky is that (as shown above) any given item may have many descendants and many ancestors. As such, I’m not sure that regular recursion in MySQL would work.
I did have a look at both of the articles linked to in the top answer for the following SO thread, but I don’t think that the suggested solutions will work for my desired data structure:
Mysql recursion?

Is there any way to structure this kind of data into a MySQL DB so that with fairly easy and lightweight queries (i.e., hopefully one query per item request), I can get the information and structure I’m looking for?
Thank you very much.

Answer

Answer by Starx

Starx

I have a suggestion.

Store item in the following structure.

+---------+-----------+
|   id    |    item   |
+---------+-----------+
|   1     |   item3   |
+---------+-----------+

And add the link references in the following

+---------+-----------+------------+
|  itemid |  ancestor | descendant |
+---------+-----------+------------+
|  1      |  3        | 2          |
+---------+-----------+------------+
|  1      |  5        | 7          |
+---------+-----------+------------+

Create a index on all three columns. This will enable you to add same time as many times as it appears on a chain.
Also you can query a particular item to find all its related links.

Question 2

Question by Josh Smith

Josh Smith

Say you have a table schema such as this: friends(friendship_id, uid1, uid2, are_friends).

When one friend un-friends another person, is it best practice to delete that row from the database (and reset auto-incrementing)? Or should you simply change the are_friends status to ‘N’?

I’d love to know what you think and why.

EDIT: My primary concern is showing some information about the state of the friends, and also showing who friended who. That way at the time of the add I can notify the friended of the friender’s request and yet not have them be friends yet.

I had originally had are_friends as a large ENUM with more options. But in that ENUM I had options such as removed and blocked, and I wasn’t sure whether that was truly useful data or not. Any further thoughts on the merits of having more vs. less data?

Also, an added question I’ve had since I posted is how one should avoid duplicating friendships. Should you make (uid1, uid2) a primary key?

Answer

Answer by fencliff

fencliff

Is there any data associated with the friend record you might want to restore, if the user re-friends the other user?

If you want to track their history, or keep some metadata associated with their relationship, then keep the record.

If you don’t want to track their history, and re-friending later doesn’t require user to input any extra data, delete it. You’ll have smaller tables, easier queries, and decreased likelihood you’ll ever forget that where-clause and show the user friends they’ve already deleted.

Answer

Answer by Starx

Starx

I would suggest to remove the row, because first thing is first, it is unnecessary data to occupy space, and besides the next time the user decides to re-friend with the same friend, then we can enter the data again, if needed.

Tag: database-design

How do I structure a bunch of items that chain together in a MySQL database?

Question by HartleySan

Answer by Starx

Please fill the form - I will response as fast as I can!