Uses Null to represent bad practice?

Question

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

i.e.

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

myRelatedTable.myTableID is the FK myTable.ID

I want to do the following: if myRelatedTable.myTableID is null, my business logic will interpret this as related to all myTable strings.

The reason I want to do this is because I have the number of rows that can be inserted into myTable after creating myRelatedTable, and some lines in myRelatedTable should reference all existing rows in myTable.

Answer 1

I don't think NULL is the best way to do this, but you can use a separate tinyInt column to indicate that the row in MyRelatedTable is connected to everything in MyTable, for example. MyRelatedTable.RelatedAll. This will make it more explicit to others who need to support it. Then you can execute some kind of connection request, for example.

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 

Answer 2

I think you could agree that it would be bad to use the number 3 to represent another value of 3 .

For the same reasons, it is therefore a bad idea to use NULL to represent anything other than the absence of a value.

If you disagree and confuse NULL for any other purpose, the service programmers who come after you will not be grateful.

Answer 3

This is not a good idea, because then you cannot use the fact of “related to all elements” in SQL queries at all. At some point you will probably need / need to do this.

Answer 4

Ideally, there should be no zeros. There must be another table to represent the relationship.

If you are going to assign special values, but NULL should only ever mean "not assigned" - that is, the relationship does not exist, use negative numbers, i.e. -1 if you want to call some business-level wrappers. For all developers who are faced with this in the future, it should be obvious that -1 is an unusual value that should not be considered normal.

Answer 5

Yes, for the simple reason that NULL is not a value . Not much value; not an empty value, but nothing.

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

Answer 6

What you published, namely, that NULL in an external key, asserts a connection with all the rows in the table to which it refers is very non-standard. From head to head, I think this is fraught with dangers.

The fact that most people using NULL in FK means that it claims to refer to NONE from the rows in the reference table. This is often the case with optional relationships that may occur at zero time.

Example. We have an HR database with a table “EMPLOYEES”. We have two columns called "EmpID" and "SupervisorID". (Many people call the first column simply "ID"). Each employee in the table has an entry in the SupervisorID, with the exception of the CEO of the company. The CEO has NULL in the SupervisorID column, which means the CEO does not have a supervisor. The CEO is accountable to BOD, but this is not represented on SupervisorID.

What you can keep in mind with respect to all the rows in the table given in the table: this is a POSSIBLE relationship between the row in question and ANY ONE of the rows in the lookup table. When you begin to understand facts that are true in the real world, but unknown in the database, you open a whole large worm of worms.