带有显示顺序的文件系统(文件、文件夹)

Question

我想设计一个像os一样的文件系统，具有特定的显示顺序(序列)可更新。

我希望文件和文件夹可以在同一层，文件不一定要放在文件夹里。但在下面的设计，如果文件不在任何文件夹我不知道如何保存序列，保存在哪里??

任何建议都将是感激的

数据示例

folder(id:1)      top layer: sequence: 0
  file(id:1)                 sequence_in_folder: 0
  file(id:2)                 sequence_in_folder: 1
folder(id:2)      top layer: sequence: 1
  file(id:3)                 sequence_in_folder: 0
file(id:4)        top layer: sequence: 2 << **sequence save in which table ??**
file(id:5)        top layer: sequence: 3 << **sequence save in which table ??**
folder
id  sequence  parent_folder_id
1   0
2   1
file
id  sequence_in_folder  folder_id
1   0                   1
2   1                   1
3   0                   2
4   ?????
5   ????

模式

CREATE TABLE IF NOT EXISTS "folder"(
  "id" SERIAL NOT NULL,
  "sequence" integer NOT NULL,
  "parent_folder_id" integer Default NULL,
  PRIMARY KEY ("id")
);
CREATE TABLE IF NOT EXISTS "file"(
  "id" SERIAL NOT NULL,
  "sequence_in_folder" integer Default NULL,
  "parent_folder_id" integer NOT NULL,
  PRIMARY KEY ("id")
);

根据@Laurenz Albe的回答，不需要改变表的设计，创建一个根文件夹。
但是如何排序数据顺序的字段交叉/存在于两个表中?顺序存在于文件夹表和文件表中，如何将它们排序在一起

查询

SELECT * FROM folder fo 
  LEFT JOIN file fi ON fi.parent_folder_id = fo.id
WHERE fo.parent_folder_id = $1 AND fi.parent_folder_id = $1
  ORDER BY  fo.sequence fi.sequence ??   ;
[1]

数据例子

folder
id | sequence | parent_folder_id | name
1  | 0        |                  | root
2  | 0        | 1                | 
3  | 2        | 1                |
file
id | sequence | parent_folder_id |
1  | 1        | 1                |

输出

folder(id:1, sequence:0 name:root)
  folder(id:2, sequence:0)
  file(id:1, sequence:1)
  folder(id:3 sequence:2)

Answer 1

两个建议:

1. 引入“anonymous”

2. 将bookmerk_folder的sequence列重命名为max_sequence左右，以避免与bookmark.sequence混淆

Answer 2

补充Laurenz的回答:

统一你的书签和文件夹列，也许bookmark_node，并要求所有东西都有一个不是书签的父节点。就像

CREATE TABLE IF NOT EXISTS fsnode(
  "id" SERIAL NOT NULL,
  "name" text,
  "is_folder" bool,
  "parent_is_folder" bool not null,
  "sequence" integer NOT NULL,
  "parent_folder_id" integer Default NULL,
  CHECK (parent_is_folder),
  PRIMARY KEY ("id"),
  UNIQUE(id, is_folder), # needed for fkey below
  FOREIGN KEY (parent_folder_id, parent_is_folder) REFERENCES fsnode (id, is_folder)
);