Clever Geek Handbook

Nulls vs Guid.Empty in SQL hierarchical table

Scenerio:

I have a folder table with hierarchical entries. The row identifier is of type GUID. ParentID in tables stores the parent folder ID:

  • ID (Guid)
  • FolderName (varchar)
  • ParentID (Guid)

Question:

What's the best practice for pasting a top-level folder? Should I make the parentID column null or create a "top-level" entry (ID = Guid.Empty) and use that ID for the top-level folders?

+2


source to share


6 answers


guid.empty

is not a SQL value. Create a ParentID null

for the root level.

You will need to hard-select {00000000-0000-0000-0000-000000000000}

to fetch the root value, whereas SQL is initially aware of null

:



select * from mytbl where parentid is null

Also, explore the new hierarchyid

SQL Server 2008 datatype if you are creating a parent-child table. This might save you a bit of the hassle of creating a recursive CTE to traverse your tree.

+5


source


Definitely not specify a null column. I used to make the top-level node point to itself as its parent, but an empty guid would be more or less the same as it required a special check.



+1


source


I would either create a static guid that represents the root, or assign the ParentID as a self-promotion to denote that the folder contains itself.

0


source


To answer the question directly, you must use a Null value in the Guid field. There are two types of 1 to many relationships in Sql - 1 to many and 0/1 to many.

The correct implementation of this is to use a null value in the key field to indicate the absence of a relationship — in other words, it has a zero-to-many relationship. The fact that the relationship returned in the same table doesn't change this.

Don't use zero-guid - that's bad practice in my opinion. What you need to do is capture any use of null characters in your code to make sure the null value is replaced. I created a database function to translate Guid.Empty value to Null Database (for Sql Server you don't specify db implementation). You could follow this idea for other database platforms.

Link here: http://www.ifinity.com.au/Blog/EntryId/81/Detecting-an-Empty-Guid-in-Sql-Server-in-T-Sql-Syntax

0


source


This is not really just a C # question. Most software developers have come across this solution. My choice is using null as the top parent. For the sake,

Scenario 1: If we don't use NULL for the PARENTID column,

1-) First of all, we have to specify the foreign key relationship for the table between parentid and id columns

2) The first record (Top parent) PARENTID must be equal to its own identifier,

so it's bad practice because it causes an infinite loop in the relation.

Scenario 2. If we use a PARULTID column with the NULL option,

1) Most DBMSs, fortunately, allow null values ​​for foreign keys.

2) This allows the first or any other record to be the main parent row by simply having null for PARENTID.

Therefore, we don't need to create an infinite loop. I think there is no way to forget about the null check or forget about the special value. We already have a special value, this is a null value :)

Some of the projects I describe earlier used Empty Guid. And I see items like "Please select", "Select" in the reports. Because its main parent (Empty Guid). So I need to check this.

0


source


Using null for a special value is bad practice and anti-pattern.

Null already has a value in the database that is not a value. All database systems are programmed for this (according to the SQL standard, which never evaluates operations with a null value to true (except in a special case) is [not] null ").

So, choose the special meaning that works best for your case and use it.

-2


source






More articles:


All Articles