3

I'm working on some sort of social network these days, and I'm wondering how I can store the user connections in a database.

The structure I've been thinking of looks like this, and it suits me pretty good. I guess it's against the third rule of DB-normalization, but I don't see a better way to store the connections since there will be a big amount of users (hopefully ;) ) and it doesn't make sense to create a new column for every new connection.

table-name: tbl_users


user_id                username                connected_users
----------------       ----------------        --------------------
  1                      Freak                  2,4
  2                      Banana                 1,3,5
  3                      MacKing                1,2,4,5
  4                      Nightmare      
  5                      Dreamer                2,3
  ...                    ...                    ...

What I'm looking for is:

- a way to add and/or delete single values from the 'connected_users' column (for example when a user is on another user's profile and clicks the button 'connect-with' or 'delete-connection')

and to find connections, for example when a user writes a post (button 'new-post'), to find all the authors current connections and save them to my other table: 'tbl_blogposts' (seems strange, but I want those current connections to be stored within the post and never be changed again, no matter whom the author is connected to, later):

table-name: tbl_blogposts


post_id                author                connected_users
----------------       ----------------        --------------------
  1                      1                      2,4
  2                      3                      1,2,4,5
  3                      3                      1,2,4,5
  4                      5                      2,3
  5                      2                      1,3,5
  ...                    ...                    ...

or

- a better way to store all these connections

maxischl
  • 579
  • 1
  • 11
  • 29
  • A relational database model and engine are not really the tools best suited for such task. Take a look at graph databases instead. They are specialized on such tasks. – arkascha Dec 12 '16 at 21:15
  • never heard of that, so far. i'm pretty new to programming, just started php two weeks ago, meaning my knowledge is quite limited and I'd appreciate it really much to find a way roundabout the stuff i know so far ;) but i'll check it out anyways! – maxischl Dec 12 '16 at 21:19
  • 1
    No, it doesn't make sense to create a new column for every connection; that would make for some seriously heinous queries in your app. However, your current approach will also make for some heinous queries anyways. What you should do is create a new table called `tbl_user_connections` with two columns: `user_id` and `connection_user_id`. Now insert one row for each connected user. Now, upon writing a post you will be able to quickly query the new table and pull through a static list of connections which you can associate to the post. – MonkeyZeus Dec 12 '16 at 21:21
  • @arkascha Very interesting, would you be able to provide a solid recommendation? It seems to be a very new and volatile market right now. Which one does PHP support? – MonkeyZeus Dec 12 '16 at 21:24
  • Listen to @MonkeyZeus. This is the standard way to relate data. – AbraCadaver Dec 12 '16 at 21:24
  • In addition to my above comment, you should also normalize your `tbl_blogposts` in a similar fashion. Remove `connected_users` from that table and make a `tbl_blogposts_connections` table with `post_id` and `connection_user_id`. – MonkeyZeus Dec 12 '16 at 21:27
  • Wait, someone actually wrote that relational database isn't the right tool for establishing connections between people? As in, **RELATIONS**. Holy shit, they let everyone think they're programmers these days. This is actually such a trivial task, and if you're two weeks into programming you go with a simple junction table. `from_id` and `to_id` in a single table, that lets you create as many relations between people as you need. You add **rows**. You don't add columns. You don't store comma separated list in a column. And yes, you want A LOT of rows. It's easy to handle for MySQL. – N.B. Dec 12 '16 at 21:34
  • @N.B.: I think you're mixing up two things here. With the tables I posted the only way to insert connections is by adding columns for every new connection, wich is a nightmare as MonkeyZeus already pointed out. that for he gave me the hint how to do it the right way - thanks for that! And AbraCadaver just brough in a whole new way to store connections with graph databases, wich indeed seems to be a good idea but is probably a bit to complex for me, so far. Thanks for your effort anyway, your way is pretty much what MonkeyZeus told me earlier, is that right? – maxischl Dec 12 '16 at 21:39
  • 1
    @Maxischl exactly, it's the exact same thing what MonkeyZeus wrote, I forgot to clarify I'm agreeing with him. You don't need graph databases, at all. You have the standard way to relate data and you have a topic for later on when you feel more interested about graph databases. Good luck! – N.B. Dec 13 '16 at 18:27

0 Answers0