数据库设计心得 --- 树形结构

背景:
树型结构表的设计在实践中, 经常碰到. 如果设计不好, 则会给应用程序的实现导致很大的麻烦. 这里以文件夹、文件作为例子介绍

实现:
-- 文件夹信息表
create table t_folder
(
folder_id number primary key, -- 文件夹ID
folder_name varchar(50),
parent_folder_id_dir varchar(256) default '/' not null  -- 上级文件夹ID目录, 格式"1/2/", "/"表示顶级文件夹
);

-- 文件信息表
create table t_file
(
file_id number primary key,
file_name varchar(50),
folder_id_dir varchar(256) default '/' not null -- 文件所在文件夹目录串, 以字符/分隔, 默认
);

-- 例子语句
insert into t_folder(folder_id, folder_name, parent_folder_id_dir) values (1, '1_folder_name', '/');
insert into t_folder(folder_id, folder_name, parent_folder_id_dir) values (2, '2_folder_name', '1/');
insert into t_folder(folder_id, folder_name, parent_folder_id_dir) values (3, '3_folder_name', '1/');
insert into t_folder(folder_id, folder_name, parent_folder_id_dir) values (4, '4_folder_name', '1/3/');

insert into t_file(file_id, file_name, folder_id_dir) values (1, '1_file_name', '1/');
insert into t_file(file_id, file_name, folder_id_dir) values (2, '2_file_name', '1/3/');
insert into t_file(file_id, file_name, folder_id_dir) values (3, '3_file_name', '1/2/');
insert into t_file(file_id, file_name, folder_id_dir) values (4, '4_file_name', '1/2/');

问题:
1. 获取文件夹目录结构
select parent_folder_id_dir from t_folder where folder_id = 4;
2. 获取文件夹及子文件夹下所有文件
select * from t_file where folder_id_dir like '1/2/%';
注：此语句可以利用索引(字符串前缀模糊配置)

设计:
1. parent_folder_id_dir取值为上级文件夹ID目录串, 解决"根据folder_id获取文件夹目录结构"需求, 非常方便
2. folder_id_dir取值为文件夹ID目录串, 解决"递归获取文件夹及子文件夹下的所有文件"需求, 非常方便

备注：
1. 如果t_folder表中parent_folder_id_dir字段取成parent_folder_id, 如果需要获取文件夹目录结构, 则需要逐级向上查询t_folder表
2. 如果t_file表中folder_id_dir字段取成folder_id, 如果需要获取文件夹及子文件夹下所有文件时, 则需要递归逐级向下查询t_file表