在关系数据库中存储文件夹层次结构 [英] Storing folder hierarchy in relational database

问题描述

我有一些表示文件夹的对象，我想知道它们是否应该在数据库中表示。

I have objects representing folders and I'm wondering if they should be represented in the database.

一方面，似乎最简单的方法是不代表文件夹对象，而只是存储文件夹中包含的对象的路径值。我看到的问题是，您不能保留其后代不包含任何项目的文件夹，这关系不大。另外我也不清楚如何在不将所有内容预先加载到内存的情况下加载要显示的文件夹层次结构（例如在TreeView中），这可能是性能问题。

On the one hand it seems like the easiest way would be to not represent folder objects and just store a path value for objects contained in a folder. Problems I see with this is that you can't persist an folder whose descendants do not contain any items, which isn't too big of a deal. Also I don't have a clear idea of how to load the folder hierarchy to display (such as in a TreeView) without loading everything into memory upfront, which would probably be a performance issue.

另一种方法是有一个文件夹表，该表引用其父文件夹。看来应该可以，但是我不确定只要不共享父文件夹，如何允许使用相同名称的文件夹。

The alternative is to have a "Folder" table with references to its parent folder. This seems like it should work, but I'm unsure how to allow folders with the same name as long as they do not share a parent. Should that even be something the DB should be concerning itself with or is that something that I should just enforce in the the business logic?

推荐答案

这个想法是这样的（自我引用）：

The idea is something like this (self-referencing):

CREATE TABLE FileSystemObject ( 
    ID int not null primary key identity,
    Name varchar(100) not null,
    ParentID int null references FileSystemObject(ID),
    constraint uk_Path UNIQUE (Name, ParentID),
    IsFolder bit not null
)

这篇关于在关系数据库中存储文件夹层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！