How does one design a database which has a table with 2 fields and one of them could be set at any given time, without an excessive amount of redundancy? For instance personal files system:

Let us state that there exists a table with drives, one with folders and something with files.

Drives and files are very trivial. However, folders possess a parent which may be whether folder (by which situation the reference would be to exactly the same table) or perhaps a disk (by which situation the reference would be to a disk row).

Can you then add extra tables?

One table with id, title, parentid, and (if you would like) type. Folders and files all indicate their parent with parentid. Disks don't have a parentid. The only real area that's NULL may be the parentid area from the (relatively couple of) records which are of type Disk.

Minor Error Remedied 26 November 10

I don't understand why you'll need three files (they aren't "tables" inside a database until they're normalised together).

When the content you're really coping with drives folders files, then they all are files. This can lead to the famous directory tree problem, that was solved in Unix I-Nodes, but remains an issue on any MS file system.

  • The greatest level Nodes have FileNames from the form "X:" (a Drive)

    • They've no Parent Nodes

    • Use CHAR() rather than Nulls (this is a substitute, getting rid of the issues of Nulls within the database, and for that reason getting rid of the performance problem it's not fixing the Null Problem, because Drives don't have any parent. You'll still need code).

  • Not 2 or 3 separate and disparate files with massive data duplication increase anomalies (you probably did request for any database-design answer, right ?). It's not trivial.

  • No blindly adhering Id-iot "key" posts on exactly what moves.

  • If IsFolder, the Node is really a branch within the directory tree, a Folder othe smart it's a File.

  • Files could be marketed to Folders: set 'IsFolder' to true then then can be used a ParentNode. For those who have ISO/IEC/ANSI standard SQL, you are able to implement a CHECK constraint to make sure ParentNodes are IsFolder

  • In which the row isn't IsFolder, personal files, it can't be utilized for a Folder or like a ParentNode.

  • Filenames should be unique inside the Node (Folder), therefore a special Index is provided. AK is Standard notation for Alternate Key meaning Unique Index.

Directory Data Model

â–¶Link to IDEF1X Notationâ—€ for visitors who don't know the Relational Modelling Standard.

Reaction to Comments

  1. Some commenters believe that there's "duplication" within this model or the FileName is concatenated. This really is apparently because of the inability to browse the model, that is provided in Standard notation. Or even the lack of ability to determine highly Normalised structures.

    • there's no duplication of any sort

    • FileNames are atomic, eg CHAR(30), not the whole path or URL.

    • Atomic means the FileNames are not concatenated

    • FileNames are not copied inside the node. When the same FileName can be used in another Node, that's not really a duplication, that's the truth, which is permitted.

    • Highly Normalised structures are actually, really small, and terse.

  2. I'd request anybody getting difficulty with understanding this model to publish a particular question, instead of to create claims about subjects they don't know.

  3. Anybody who thinks they've found a "problem" with this particular model is asked for some thing just like a technical person, and publish the specific error they think they've found, instead of to publish generalities and private opinions.