so this is a logic question as to creating the proper relationships in a MySQL database.What logic would you choose for the following scenario?This is a back-end for an app where people can have friends they can interact with, or Add if you will to their own Network for lack of better terms.So, for example, should I have a MyFriends table where, for example, the MyFriend.ID column is identical to perhaps the User.ID of the person that initiated the connection? And have a subsequent column called Friends where the User.ID of everyone you Added to your Network is inserted?Or do you have alternate suggestions as to create the Relationships necessary for the Friends to be connected inside each owns Network?
RDBMS (MySQL) logic question. How to join multiple FRIENDS
database design;mysql;relational database;rdbms
I'd have one table for Users and a second table for Relationships.The Relationship table would have 2 foreign keys, both pointing back to records in the User table.Here's a rough diagram:+------------------+         +------------------------+| User             |         | Relationship           ||------------------|         |------------------------|| UserPK  (int)    |         | RelationshipPK (int)   || ...              |<--------+ User1FK        (int)   ||                  |<--------+ User2FK        (int)   ||                  |         |                        ||                  |         |                        ||                  |         |                        ||                  |         |                        |+------------------+         +------------------------+The Relationship table is technically the middle table in a many to many relationship.Here's an example.  Note the diagram shows 2 records from the USER table:+------------------+         +------------------------+      +------------------+| User             |         | Relationship           |      | User             ||------------------|         |------------------------|      |------------------|| UserPK:     1    |         | RelationshipPK   100   |      | UserPK:     2    || Name:   ALICE    |<--------+ User1FK            1   |      | Name:     BOB    ||                  |         | User2FK            2   +----->|                  ||                  |         |                        |      |                  ||                  |         |                        |      |                  ||                  |         |                        |      |                  ||                  |         |                        |      |                  |+------------------+         +------------------------+      +------------------+We have 3 records in the database: a USER record for Alice, a USER record for Bob, and a RELATIONSHIP record showing that Alice and Bob know each other.You'll probably want to add additional fields to these tables.  For example, is the relationship friends, co workers, family, etc.If Alice is a friend of Bob and Bob is a friend of Alice (i.e., a symmetrical relationship), it might be easiest to just have two records in the relationship table (User1FK:Alice, User2FK:Bob and User1FK:Bob, User2FK:Alice).  That would make certain queries easier.