Matthew

>> How long do you think it will be before you start defaulting strings
>> to ''?
>
>You already showed me the light with defaults, so "never"! 8-)

I understand each word in that sentence, unfortunately I cannot make them combine in a way which imparts anything comprehensible!

Roy Lambert

Roy Lambert wrote:

> >> How long do you think it will be before you start defaulting
> strings >> to ''?
> >
> > You already showed me the light with defaults, so "never"! 8-)
>
> I understand each word in that sentence, unfortunately I cannot make
> them combine in a way which imparts anything comprehensible!

Hmm, I can't find the thread, but I was sure it was you that helped me
with my conversion from DBISAM to ElevateDB. One thing I was falling
over on was the empty string not being the same as NULL, and that the
fix for it was to set a default when creating a new record so that the
field would have an empty string, and not therefore be NULL.

--

Matthew Jones

Matthew

Roy Lambert

"Matthew Jones" <matthew@mattNOSP-AMhew-jones.com> wrote on Fri, 4 Jul 2014 08:43:36 -0400

>Roy Lambert wrote:
>
>> >> How long do you think it will be before you start defaulting
>> strings >> to ''?
>> >
>> > You already showed me the light with defaults, so "never"! 8-)
>>
>> I understand each word in that sentence, unfortunately I cannot make
>> them combine in a way which imparts anything comprehensible!
>
>Hmm, I can't find the thread, but I was sure it was you that helped me
>with my conversion from DBISAM to ElevateDB. One thing I was falling
>over on was the empty string not being the same as NULL, and that the
>fix for it was to set a default when creating a new record so that the
>field would have an empty string, and not therefore be NULL.

So you've already started. That certainly contradicts the never <vbg>

Roy Lambert

Roy Lambert wrote:

> So you've already started. That certainly contradicts the never <vbg>

I made a super-hash of that post didn't I! What I meant was, I don't
see myself ever enabling the automatic option to do that. I have got
columns with NULLs that are indeed different from empty strings.

--

Matthew Jones

Matthew

>> So you've already started. That certainly contradicts the never <vbg>
>
>I made a super-hash of that post didn't I! What I meant was, I don't
>see myself ever enabling the automatic option to do that. I have got
>columns with NULLs that are indeed different from empty strings.

Sorry about this but I have to ask - what are they. I'm still desperately seeking a real use case that makes sense (to me)

Roy

Roy Lambert wrote:

> Sorry about this but I have to ask - what are they. I'm still
> desperately seeking a real use case that makes sense (to me)

They are things like "middle name" and "organisation". Nothing fancy,
but NULL simply means that they haven't been specified. I have fields
like VATOverride, filenames, that are NULL if there is no valid value.

I guess you could argue that an empty string also indicates the same,
but NULL is much clearer. I'll have to look at my code to see where
I've done conditionals on it.

--

Matthew Jones

Roy

>>How did your system address this issue? Single name CLOB column so they can have as long a name as they >>wanted (or as short obviously)? How did you notify users on screen (or on printout) of the difference between >>NULL and emptystring?

In practice a VARCHAR(255) was enough. Arabs can exceed that by dumping their entire genealogy as a surname via a huge list of "ibn" but in practice no-one does that except Royalty in very formal cases.

The general solution in the app I'm discussing is that all fields besides fixed combos and the like respond to a context menu and Ctrl+N and Ctrl+U as hot keys. The users are familiar with this as it crops up in other cases like middle names, maiden names and so on. On a new record the TEdits (etc.) default to blank/clWindow, press Ctrl+N (None/Not Applicable) and the TEdit will switch to clBtnFace/clGrayText and read "<N/A>. Press Ctrl-U (Unknown/Undefined) and the field will revert to initial state. At table level the former case is EmptyStr and the latter is NULL.

>>I'm really hoping that you're is a real example and has been designed correctly because I'd love to believe that >>there is a justification for null <> emptystring and all of the additional messing around that has to be done >>because of it.

As per my original example, the 'messing around' would occur if they were the same. I used Burmese surnames as an example just for variety: a much more common case would be NULL maiden name vs. EmptyStr maiden name (former "No idea", latter "Never married or refuses to answer").

>>Also while you're at it please tell me what's wrong with special "flag" fields? Isn't it better to have something >>there with a definitive meaning. eg this person is Burmese and doesn't need a surname (but one would be nice if >>we could get it) rather than "oh I just can't be bothered to enter anything here - its easier and the system won't >>care"!

Flag fields add complexity and in most real world cases aren't required if you apply trivalent (Value, No value, NULL) logic consistently. Also, many people won't implement flag fields correctly - they will bolt them in to the same table and break normalisation rules (the flag is not dependent on the key, the whole key and nothing but the key).

A related example, one of my applications tracks the status of the door of a cabinet in a datacentre. One might think this is a simple boolean - Open/Closed. Not in reality:

dsUnknown: Door sensor malfunctioning.

dsOpen: Door is positively known to be open.

dsClosed: Door is positively known to be closed.

This matters because a security check report might otherwise end up as:

SELECT * FROM RACKS WHERE DoorOpen=True

...which would falsely imply that everything that isn't Open is known to be Closed.

It should be:

SELECT * FROM RACKS Where DoorOpen<>False

...which will give you both the known to be Opens and the Unknown/Defective sensors. You can also run a sensor maintenance report by querying for IS NULL. Obviously if you default to the logical equivalent of EmptyStr for Booleans (i.e. 'False' in Pascal) then you have a whacking great security hole as all dodgy sensors appear as positive secure confirmations.

Eryk

Matthew


>I guess you could argue that an empty string also indicates the same,
>but NULL is much clearer. I'll have to look at my code to see where
>I've done conditionals on it.

I don't know about you but I certainly could (and would)

Roy

Eryk

Roy Lambert

Eryk Bottomley wrote on Fri, 4 Jul 2014 15:35:52 -0400

>Roy
>
>>>How did your system address this issue? Single name CLOB column so they can have as long a name as they >>wanted (or as short obviously)? How did you notify users on screen (or on printout) of the difference between >>NULL and emptystring?
>
>In practice a VARCHAR(255) was enough. Arabs can exceed that by dumping their entire genealogy as a surname via a huge list of "ibn" but in practice no-one does that except Royalty in very formal cases.
>
>The general solution in the app I'm discussing is that all fields besides fixed combos and the like respond to a context menu and Ctrl+N and Ctrl+U as hot keys. The users are familiar with this as it crops up in other cases like middle names, maiden names and so on. On a new record the TEdits (etc.) default to blank/clWindow, press Ctrl+N (None/Not Applicable) and the TEdit will switch to clBtnFace/clGrayText and read "<N/A>. Press Ctrl-U (Unknown/Undefined) and the field will revert to initial state. At table level the former case is EmptyStr and the latter is NULL.

At least that conforms to my definition of user friendly system = what I'm used to

>>>I'm really hoping that you're is a real example and has been designed correctly because I'd love to believe that >>there is a justification for null <> emptystring and all of the additional messing around that has to be done >>because of it.
>
>As per my original example, the 'messing around' would occur if they were the same. I used Burmese surnames as an example just for variety: a much more common case would be NULL maiden name vs. EmptyStr maiden name (former "No idea", latter "Never married or refuses to answer").

Here I must disagree with you. The messing around is in the interface because you present (None/Not Applicable) and (Unknown/Undefined) as different to the user so you are adding complexity to a system by having to translate between the database and the human being.

>>>Also while you're at it please tell me what's wrong with special "flag" fields? Isn't it better to have something >>there with a definitive meaning. eg this person is Burmese and doesn't need a surname (but one would be nice if >>we could get it) rather than "oh I just can't be bothered to enter anything here - its easier and the system won't >>care"!
>
>Flag fields add complexity and in most real world cases aren't required if you apply trivalent (Value, No value, NULL) logic consistently. Also, many people won't implement flag fields correctly - they will bolt them in to the same table and break normalisation rules (the flag is not dependent on the key, the whole key and nothing but the key).

Firstly I disagree about adding complexity but I'm very puzzled about tristate fields unless you're specifically referring to booleans being true/false/unknown.

>A related example, one of my applications tracks the status of the door of a cabinet in a datacentre. One might think this is a simple boolean - Open/Closed. Not in reality:
>
>dsUnknown: Door sensor malfunctioning.
>
>dsOpen: Door is positively known to be open.
>
>dsClosed: Door is positively known to be closed.
>
>This matters because a security check report might otherwise end up as:
>
>SELECT * FROM RACKS WHERE DoorOpen=True
>
>..which would falsely imply that everything that isn't Open is known to be Closed.
>
>It should be:
>
>SELECT * FROM RACKS Where DoorOpen<>False
>
>..which will give you both the known to be Opens and the Unknown/Defective sensors. You can also run a sensor maintenance report by querying for IS NULL. Obviously if you default to the logical equivalent of EmptyStr for Booleans (i.e. 'False' in Pascal) then you have a whacking great security hole as all dodgy sensors appear as positive secure confirmations.

So what you seem to say here is that you are using a boolean column and treating NULL as though it has a meaning of dsUnknown. Is that right? If my assumption is correct I would much prefer an integer column with distinct values.

I think one of our differences is that I much prefer a positive mechanism to identify things whereas you are imputing meaning to  NULL as a substitute for such  a positive mechanism.

Roy