root in tree like structure with auto-referencing foreign keys

Question

Hi.

My application uses some kind of "virtual folders" so I need to store the tree structure in the database. The table holding the data is quite simple, it has 3 columns:

• id_folder (int, primary key)
• id_parent (int, references id_folder)
• folder_name (string)

My question is: which is the best way to implement the root?

1. Making id_parent nullable; the root will be the folder with id_parent=null, or
2. Making the folder to be its own parent, i.e., id_folder=id_parent.

Thank you.

Answer 1

It really depends on whether you have additional requirements. I like NULL, as it makes logical sense, but other requirements may call for something else.

Answer 2

I would argue for option 1.

If you choose option 2, then your logic for displaying sub-folders would need confusing checks to make sure that the id_folder and id_parent don't match.

Option 1:

SELECT * --don't kill me for using *, it's just for an example
FROM Folders
WHERE id_parent = @folderId

Option 2:

SELECT * --don't kill me for using *, it's just for an example
FROM Folders
WHERE id_parent = @folderId AND id_parent <> id_folder