[Solved] A relationship database design (using MySQL)

EverSQL Database Performance Knowledge Base

A relationship database design (using MySQL)

Database type:

Recently, I am trying to design a database solution that about "user relationship"—— each user has friends, each friend own an authority(authority describe the privacy level between their relationship). I am totally new in MySQL program, here is my two database design:

design 1: databse has an "user_table" that describe all users's base information such as user_id, age, email ... ,and each user has an "friends_table", "friends_table" contains the friend's user_id(can be queried the friend's information), each "friend_table" can be queried by its table name stored in "user_table"(each friend_table's name is unique).

user_table
+------------------------+
| user_name(primary key) |
+------------------------+
|        password        |
+------------------------+
|          email         |
+------------------------+
|         ....           |
+------------------------+
|         ....           |             friend_table
+------------------------+ ----------> +------------------------------+
|    friend_table_name   |             |          id(primary key)     |
+------------------------+             +------------------------------+
                                       |          user_name           |
                                       +------------------------------+
                                       |          authority           |
                                       +------------------------------+
                                       |             ....             |
                                       +------------------------------+

design 2: databse has an "user_table" and an "relationship_table". the "relationship_table" describe all user's relationship.

user_table                          relationship_table
+------------------------+          +------------------------+
| user_name(primary key) |          |     id(primary key)    |
+------------------------+          +------------------------+
|        password        |          |        user_name       |
+------------------------+          +------------------------+
|          email         |          |       friend_name      |
+------------------------+          +------------------------+
|         ....           |          |        authority       |
+------------------------+          +------------------------+
|         ....           |          |          ....          |
+------------------------+          +------------------------+

base on design 2, a user named Jim has 4 friend Lisa, Tom, Jerry and Johnny, the tbales show as below:

user_table            
+-----------+-----------+---------------+
| user_name | password  |      email    |
+-----------+-----------+---------------+
|    Jim    |   *****   |  [email protected] |
+-----------+-----------+---------------+
|   LiLei   |   *****   |  [email protected] |
+-----------+-----------+---------------+
|   ....    |   *****   |     ....      |
+-----------+-----------+---------------+
|   ....    |   *****   |     ....      |
+-----------+-----------+---------------+

relationship_table            
+-----------+-----------+---------------+---------------+
|     id    | user_name |  friend_name  |   authority   |
+-----------+-----------+---------------+---------------+
|     1     |    Jim    |      Lisa     |     ***       |
+-----------+-----------+---------------+---------------+
|     2     |    Jim    |      Tom      |     ***       |
+-----------+-----------+---------------+---------------+
|     3     |    Jim    |     Jerry     |     ***       |
+-----------+-----------+---------------+---------------+
|     4     |    Jim    |     Johnny    |     ***       |
+-----------+-----------+---------------+---------------+
|     5     |    Qing   |      Jim      |     ***       |
+-----------+-----------+---------------+---------------+
|     6     |    Feng   |      Tom      |     ***       |
+-----------+-----------+---------------+---------------+
|     7     |    Guang  |      Tom      |     ***       |
+-----------+-----------+---------------+---------------+

if I want to query Jim's friend, I use the SQL command:

 select friend_name from relationship_table where user_name='Jim';

consider to the Database performance, these 2 designs which is the better one? or there is a better design exist?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `relationship_table` ADD INDEX `relationship_table_idx_user_name` (`user_name`);
The optimized query:
SELECT
        relationship_table.friend_name 
    FROM
        relationship_table 
    WHERE
        relationship_table.user_name = 'Jim'

Related Articles



* original question posted on StackOverflow here.