Table with 2 fields but only one can be used at a time

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 ca开发者_如何转开发n 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?

---

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.

---

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 CHECK constraint to ensure ParentNodes are IsFolder

• Where 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

1. 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.

2. 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.

3. 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.