CODEWITHSUNDEEP
sqldatabase-design
Element
Element

Reputation: 4041

Is using Null to represent a Value Bad Practice?

If I use null as a representation of everything in a database table is that bad practice ?

i.e.

I have the tables: myTable(ID) and myRelatedTable(ID,myTableID)

myRelatedTable.myTableID is a FK of myTable.ID

What I want to accomplish is: if myRelatedTable.myTableID is null then my business logic will interpret that as being linked to all myTable rows.

The reason I want to do this is because I have an uknown amount of rows that could be inserted into myTable after the myRelatedTable row is created and some rows in the myRelatedTable need to reference all existing rows in myTable.

Upvotes: 4

Views: 2055

Answers (6)

Walter Mitty
Walter Mitty

Reputation: 18940

What you posted, namely that a NULL in a foreign key asserts a relationship with all the rows in the referenced table, is very non standard. Off the top of my head, I think it's fraught with dangers.

What most people who use NULLs in FKs mean by it is that it asserts a relationship to NONE of the rows in the referenced table. This is common in the case of optional relationships, ones that can occur zero times.

Example: We have an HR database, with a table called "EMPLOYEES". We have two columns, called "EmpID" and "SupervisorID". (Many people call the first column simply "ID"). Every employee in the table has an entry under SupervisorID with the sole exception of the CEO of the company. THe CEO has a NULL in the SupervisorID column, meaning that the CEO has no supervisor. The CEO is accountable to the BOD, but that isn't represented in SupervisorID.

What you might mean by a relationship with ALL the rows in the refernced table is this: There's a POSSIBLE relationship between the row in question and ANY ONE of the rows in the reference table. When you start to get into the questions of the facts that are true in the real world but unknown to the database you open a whole big can of worms.

Upvotes: 0

dice
dice

Reputation: 2880

Ideally there should be no nulls at all. There should be another table to represent the relation.

If you are going to assign special meanings however NULL should only ever mean "not assigned" - ie no relationship exists, use negative numbers, ie -1 if you want to trigger some business layer trickery. It should be obvious to any developers that come across this in the future that -1 is an extraordinary value that should not be treated as normal.

Upvotes: 1

Rob
Rob

Reputation: 48369

Yes, for the simple reason that NULL represents no value. Not a special value; not a blank value, but nothing.

If the foreign key is just a simple integer, and it's generated automatically, then you could use 0 to represent the "magic" value.

Upvotes: 0

Turnkey
Turnkey

Reputation: 9406

I don't think NULL is the best way to do it but you might use a separate tinyInt column to indicate that the row in MyRelatedTable is related to everything in MyTable, e.g. MyRelatedTable.RelatedAll. That would make it more explicit for other that have to maintain it. Then you could do some sort of Union query e.g.

SELECT M.ID, R.ID AS RelatedTableID,....
FROM MyTable M INNER JOIN MyRelated Table R ON R.myTableId = M.Id
UNION
SELECT M.ID, R.ID AS RelatedTableID,....
FROM MyTable M, MyRelatedTable R
WHERE R.RelatedAll = 1

Upvotes: 0

Ed Guiness
Ed Guiness

Reputation: 35267

I think you might agree that it would be bad to use the number 3 to represent a value other an 3.

By the same reasoning it is therefore a bad idea to use NULL to represent anything other than the absence of a value.

If you disagree and twist NULL to some other purpose, the maintenance programmers that come after you will not be grateful.

Upvotes: 13

Michael Borgwardt
Michael Borgwardt

Reputation: 346300

Not a good idea, because then you cannot use the "related to all entries" fact in SQL queries at all. At some point, you'll probably want/need to do this.

Upvotes: 1

Related Questions