表有 2 个字段，但一次只能使用一个
您将如何设计一个数据库，该数据库有一个包含 2 个字段的表，并且一次只能设置其中一个字段，而没有太多冗余？例如文件系统：
假设我们有一张包含驱动器的表，一张包含文件夹，一张包含文件。
驱动器和文件非常简单。但是，文件夹有一个父级，它可以是文件夹（在这种情况下引用是对同一个表）或磁盘（在这种情况下引用是对磁盘行）。
您会添加一些额外的桌子吗？
How would you design a database that has a table with 2 fields and just one of them can be set at a time, without too much redundancy? For example a file system:
Let's say that we have a table with drives, one with folders and one with files.
Drives and files are quite trivial. However, folders have a parent which can be either a folder (in which case the reference is to the same table) or a disk (in which case the reference is to a disk row).
Would you add some extra tables?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一张包含 id、name、parentid 和（如果需要）类型的表。文件夹和文件都通过parentid 指向其父级。磁盘没有parentid。唯一为 NULL 的字段是磁盘类型的（相对较少的）记录的parentid 字段。
One table with id, name, parentid, and (if you want) type. Folders and files all point to their parent with parentid. Disks do not have a parentid. The only field that is NULL is the parentid field of the (relatively few) records that are of type Disk.
于 2010 年 11 月 26 日更正小错误
我不明白为什么您需要三个文件（在将它们一起规范化之前，它们不是数据库中的“表”）。
如果您实际处理的内容有驱动力；文件夹；文件，那么它们都是文件。这导致了著名的目录树问题，该问题在 Unix I-Nodes 中得到了解决，但在任何 MS 文件系统上仍然是一个问题。
最高级别的节点具有“X:”（驱动器）形式的文件名
它们没有父节点
使用 CHAR(0) 代替 Null（这是一种替代品，消除了数据库中 Null 的问题，从而消除了性能问题；它并没有解决 Null 问题，因为驱动器没有父级。您仍然需要代码）。
.
不是两三个独立且不同的文件存在大量数据重复和更新异常（您确实要求数据库设计答案，对吧？）。这不是微不足道的。
不要盲目地将 Id-iot“关键”列粘贴在所有移动的东西上。
如果
IsFolder，则该Node是目录树中的一个分支，一个Folder；否则它是一个文件。文件可以提升到文件夹：将“IsFolder”设置为 true；之后then可以用作ParentNode。如果您有 ISO/IEC/ANSI 标准 SQL，则可以实现
CHECK约束以确保 ParentNodes 是IsFolder行不是
IsFoldercode>，一个文件，它不能用作文件夹或父节点。文件名在节点（文件夹）内必须是唯一的，因此提供了唯一索引。 AK 是备用键的标准表示法，表示唯一索引。
目录数据模型 http://www.softwaregems.com.au /Documents/Student%20Resolutions/Andrew%20DM.pdf
▶链接到 IDEF1X 表示法◀，适合不熟悉关系建模标准的读者。
对评论的回应
一些评论者认为该模型中存在“重复”或者文件名是串联的。这显然是由于无法读取以标准表示法提供的模型。或者无法识别高度标准化的结构。
没有任何形式的重复
文件名是原子的，例如 CHAR(30)，而不是整个路径或 URL。
原子意味着文件名未连接
文件名在节点内不重复。如果在另一个 Node 中使用相同的 FileName，那不是重复，这是事实，这是允许的。
高度标准化的结构实际上非常小且简洁。
我会要求任何难以理解此模型的人发布一个具体问题，而不是对他们不知道的主题做出陈述。
任何认为自己发现了该模型“问题”的人同样被要求像技术人员一样行事，并发布他们认为发现的具体错误，而不是发布笼统的内容和个人意见。
Minor Error Corrected 26 Nov 10
I do not see why you need three files (they are not "tables" in a database until they are normalised together).
If the content you are actually dealing with drives; folders; files, then they are all files. This leads to the famous directory tree problem, which was solved in Unix I-Nodes, but remains a problem on any MS file system.
The highest level Nodes have FileNames of the form "X:" (a Drive)
They have no Parent Nodes
Use CHAR(0) instead of Nulls (that's a substitute, eliminating the problems of Nulls in the database, and therefore removing the performance issue; it is not solving the Null Problem, because Drives have no parent. You still need code).
.
Not two or three separate and disparate files with massive data duplication and update anomalies (you did ask for a database-design answer, right ?). It is not trivial.
No blindly sticking Id-iot "key" columns on everything that moves.
If
IsFolder, the Node is a branch in the directory tree, a Folder; othe wise it is a File.Files can be promoted to Folders: set 'IsFolder' to true; after which then can be used as a ParentNode. If you have ISO/IEC/ANSI standard SQL, you can implement a
CHECKconstraint to ensure ParentNodes areIsFolderWhere the row is not
IsFolder, a File, it cannot be used as a Folder or as a ParentNode.Filenames must be unique within the Node (Folder), therefore an Unique Index is supplied. AK is Standard notation for Alternate Key meaning Unique Index.
Directory Data Model http://www.softwaregems.com.au/Documents/Student%20Resolutions/Andrew%20DM.pdf
▶Link to IDEF1X Notation◀ for readers who are unfamiliar with the Relational Modelling Standard.
Response to Comments
Some commenters think that there is "duplication" in this model or that the FileName is concatenated. This is apparently due to not being able to read the model, which is provided in Standard notation. Or the inability to recognise highly Normalised structures.
there is no duplication of any kind
FileNames are atomic, eg CHAR(30), not the entire path or URL.
Atomic means the FileNames are not concatenated
FileNames are not duplicated within the node. If the same FileName is used in another Node, that is not a duplication, that is the reality, and that is allowed.
Highly Normalised structures are in fact, very small, and terse.
I would ask anyone having difficulty with understanding this model to post a specific question, rather than to make statements about subjects they do not know.
Anyone who thinks they have found a "problem" with this model is likewise requested to behave like a technical person, and post the specific error that they think they have found, rather than to post generalities and personal opinions.