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?