4 ms·

> I create all fields as NOT NULL and use empty string in place of NULL. ...but why?

habibur 4y ago
Don't have a use case for a field being NULL instead of "". Say, if I want to check how many records I don't have value for "ref", I don't want the count(*) query to show count(*) ref 12000 (null) 17030 "" I want both added together. That's for example one simple reason out of many others.
- sgjohnson 4y ago
  And how do you differentiate between an absence of value and empty value? it sounds like you're just using the database wrong.
  - habibur 4y ago
    That's the fundamental argument supporting the use of NULL. Extending this one will require you to use "infinite types of NULLs". Decades ago there was an article written explaining it, and that turned into a meme for a while. For example : Guess you set up a "collector database" that collects data from other databases. It might don't know what the other database's field value is, (call this situation "NULL", the classic case). Or know the other database field value and it's NULL on the other database (now call it NULL-KNOWN, or NULL-TYPE2). And then do the same thing for a program that now reads from this "collector database ". NULL = Program haven't read the database yet and doesn't know. NULL1 = Program have read the database and it's null. NULL2 = it was null from where this collector database read this data. See where's that going? Use a separate field if you want to distinguish.
    - sgjohnson 4y ago
      > NULL = Program haven't read the database yet and doesn't know. NULL1 = Program have read the database and it's null. NULL2 = it was null from where this collector database read this data. It's not a valid computing problem. In your example there's no difference whatsoever between NULL1 and NULL2, because it doesn't matter where it came from, and as for the difference between NULL and NULL1 - it's irrelevant, because if we know that the field exists, we have the database. > "decades ago" I'm fairly sure nobody needed more than 1 type of NULL decades ago, and certainly nobody needs more than 1 type of NULL now.
      - habibur 4y ago
        Indeed. Lesson learned. Thanks.

Don't have a use case for a field being NULL instead of "". Say, if I want to check how many records I don't have value for "ref", I don't want the count(*) query to show count(*) ref 12000 (null) 17030 "" I want both added together. That's for example one simple reason out of many others.

sgjohnson 4y ago
And how do you differentiate between an absence of value and empty value? it sounds like you're just using the database wrong.
- habibur 4y ago
  That's the fundamental argument supporting the use of NULL. Extending this one will require you to use "infinite types of NULLs". Decades ago there was an article written explaining it, and that turned into a meme for a while. For example : Guess you set up a "collector database" that collects data from other databases. It might don't know what the other database's field value is, (call this situation "NULL", the classic case). Or know the other database field value and it's NULL on the other database (now call it NULL-KNOWN, or NULL-TYPE2). And then do the same thing for a program that now reads from this "collector database ". NULL = Program haven't read the database yet and doesn't know. NULL1 = Program have read the database and it's null. NULL2 = it was null from where this collector database read this data. See where's that going? Use a separate field if you want to distinguish.
  - sgjohnson 4y ago
    > NULL = Program haven't read the database yet and doesn't know. NULL1 = Program have read the database and it's null. NULL2 = it was null from where this collector database read this data. It's not a valid computing problem. In your example there's no difference whatsoever between NULL1 and NULL2, because it doesn't matter where it came from, and as for the difference between NULL and NULL1 - it's irrelevant, because if we know that the field exists, we have the database. > "decades ago" I'm fairly sure nobody needed more than 1 type of NULL decades ago, and certainly nobody needs more than 1 type of NULL now.
    - habibur 4y ago
      Indeed. Lesson learned. Thanks.