Древовидная структура БД SQL

#mysql #sql #sql_server #sqlite

Подскажите, покажите пример, как создавать таблицы древовидных структур, и как выполнять
запрос к ним на добавление данных.

Допустим у нас есть таблица пользователей Table_Name_user:

 |Table_Name_user|
 |-------|
 |user_id|
 |-------|
 |      0|
 |-------|
 |      1|
 |-------|
 |      2|
 |-------|
 |..и.т.д|


если мы находим какого-то пользователя по user_id, допустим "2", то мы должны узнать
сколько друзей он себе добавил. Как это сделать с помощью древовидной структуры БД?

Должно получиться что у каждого user_id, есть как бы своя таблица, в которой он может
добавлять или удалять друзей. ПРИМЕЧАНИЕ: своя таблица, это не значит отдельная таблица
в базе, а значит какая-то подтаблица которая будет храниться в ячейке отдельного юзера. 

table_friend - уникальная таблица, которая есть у каждого user_id. table_friend -
не находятся отдельно в базе, они прикреплены к каждой ячейки user_id.

|Table_Name_user|
     |-------|
     |user_id|
     |-------|
     |      0|----------|table_friend|(это не отдельная таблица в базе)
     |-------|
     |      1|----------|table_friend|(это не отдельная таблица в базе)
     |-------|
     |      2|----------|table_friend|(это не отдельная таблица в базе)
     |-------|
     |..и.т.д|

    

Ответы

Ответ 1

Когда друзья не пользователи. (Когда пользователи рисовать больше в лом)
Таблицы пользователей и друзей связываются по некоторому ключевому полю. Естественно
взять в качестве ключа связи поле Id из таблицы Users.

    Users                  Frends
+-------------+       +-------------+
| Id          |<--+   | Id          |
| Name        |   +-<<| User_Id     |
| ...         |       | Name        |
|             |       | ...         |


Извлечь всех друзей пользователя номер N:
SELECT * FROM FRENDS WHERE User_Id = N  

Извлечь пользователей вместе с их друзьями:
SELECT * FFOM USERS U LEFT JOIN FRENDS F ON F.User_Id = U.Id

Добавить друга пользователю номер N:
INSERT INTO FRENDS (User_Id, ...) VALUES (N, ...)  

Удалить друзей:
DELETE FROM FRENDS WHERE User_Id = N

И где вы тут дерево нашли. Так ветка одна.

В таблице FRENDS переписаны друзья всех всех пользователей. Но у каждой записи есть
поле (User_Id), которое ссылается на пользователя, чей это друг.
В запросы вставляются условия на это поле, чтобы отделить нужных друзей от чужих.
Таким нехитрым способом получается как бы своя таблица друзей на пользователя.
Освойте этот простой приём. Все более сложные схемы опираются на него.

  Users
  Id   Name
+----+------------------+
|  1 | Серёжа           |
|  2 | Маша             |
...

  Frends
  Id   User_Id   Name      - по User_Id ясно
+----+---------+---------+
|  1 |    1    | Андрюша | - друг Серёжи
|  2 |    1    | Марина  | - подружка Серёжи
|  3 |    2    | Оксана  | - подружка Маши
...


Ответ 2

Похоже вам нужна не древовидная структура, а обычное отношение многие ко многим.

Например, таблица пользователей:

create table users
(
    user_id int primary key
)


Друзья пользователя:

create table user_friends
(
    user_id int,
    friend_id int,
    primary key (user_id, friend_id),
    foreign key (user_id) references users (user_id),
    foreign key (friend_id) references users (user_id)
)


Добавим пользователей:

insert into users (user_id) values (1)
insert into users (user_id) values (2)
insert into users (user_id) values (3)


Пользователь 1 зафрендил пользователей 2 и 3:

insert into user_friends (user_id, friend_id) values (1, 2)
insert into user_friends (user_id, friend_id) values (1, 3)


Сколько друзей у пользователя 1:

select count(friend_id)
from user_friends
where user_id = 1


Древовидная структура обычно используется, когда есть иерархия или подчинение (напр.
Директорат -> Замы -> Подчиненные), и в этом случае таблица сама на себя должна ссылаться.


Ответ 3

Делается одна таблица для обеспечения связи типа "многие-ко-многим", такого вида

create table friends( user_id, friend_id );


Где user_id - id пользователя который добавил себе друга, friend_id id друга - такого
же пользователя

Добавлять друзей элементарно, просто заносим в эту таблицу запись с соответствующими id.

Получить кол-во прямых друзей

select count(1) from friends where user_id=NN


Количество пользователей добавивших в друзья конкретного пользователя NN:

select count(1) from friends where friend_id=NN


Получение полного списка друзей с именами (они же есть в таблице пользователей) пользователя NN:

select F.friend_id, U.name
  from friends F join users U on (U.user_id=F.friend_id)
 where F.user_id=NN


А вот работать с такой таблицей именно как с древовидной, что бы одним запросом получить
всех друзей друзей и т.д. углубляясь в дерево на mysql и sqllite очень тяжело, потому
как их диалект SQL не поддерживает connect by. Можете в интернете поискать по фразе,
например mysql connect by можно найти как это обходить, но обычно это индивидуальные
решения. Тем более в вашем случае пользователь с ID 1 может добавить друга ID 2, а
второй добавит в друзья первого. т.е. в базе будут 2 записи (1,2) и (2,1). И в таком
случае алгоритм строящий дерево должен уметь обрубать ветви, что бы не зациклится на
таких ссылках.


Ответ 4

table_friend - не находятся отдельно в базе, они прикреплены к каждой ячейки user_id.


В такой ситуации в SQL Server можно использовать поле xml.

create table Users (id int, friends xml)
insert Users (id, friends) values (12345,  
                N'
                    
                    
                    
                  ')


для выборки всех best-friends/user/@id пишем примерно такой запрос:

select x.value('@id', 'nvarchar(100)') from Users 
  CROSS APPLY types.nodes('//best-friend/user') t(x)


Ответ 5

как создавать таблицы древовидных структур, и как выполнять запрос к ним на добавление
данных.


В таблицу надо добавить поле, например, ParentId. Примерно так:

create table Users (Id int, ParentId int, Name nvarchar(100))
insert Users (Id, ParentId, Name) values (0, NULL, 'u1'), (1, 0, 'u2') 



  мы должны узнать сколько друзей он себе добавил. Как это сделать с помощью древовидной
структуры БД?


Если считать, что "твой друг - это мой друг", то в такой ситуации древовидная структура
не подходит. Можно создать таблицу отношений, например, так:

create table Relations (UserId1 int, UserId2 int, Type int, Created datetime);


Такая таблица позволяет хранить любые связи между людьми.
Если надо выбрать всех потомков (т.е. детей и их детей и т.д.), то в T-SQL используются
CTE with recursion -- рекурсивные запросы.