4 ms·

Still after all this explaining you agree there is only two state instead of three. Usually it's used with the following logic for database oriented workflow.

Still after all this explaining you agree there is only two state instead of three. Usually it's used with the following logic for database oriented workflow. True = true False = false Null = unknow (This which might be a real-world case that you can model using null) Of course if there is an error the engine will thrown an exception Which is a whole other kind of signal.

jchw 4y ago
What I don't get is, why would I want there to be a third state in this boolean example? You're saying it could be useful. That's true, it could. However, we're talking about something that today is already just a boolean, such as "is_published." Even if you can envision a reason why you might want a third unknown state for "is_published", if you write a bunch of code that treats it as a boolean, without knowing what the null state might mean, you are not solving a problem. You're just modelling your data slightly less precisely. Saving a date is saving known, useful state, even if you don't need it yet. Using a nullable type for a field that can't yet be null is just adding an additional unknown state for no reason. When using Go and sqlc, what would happen is I would use a NOT NULL field for the boolean, because I'm modelling a boolean, not a tri-state. If I was modelling something where I had three states I wanted to handle, I would use an enum instead. But even if I wanted to use NULL instead of an enum that explicitly specifies the meaning of the third state, I'd rather start as NOT NULL. Why? Because then, if I ever want to remove the constraint, I can. And when I do, sqlc will helpfully change the type of the field from a raw boolean to a nullable boolean wrapper. And doing this will break the existing code. This is good. Why? Because in every place where I assumed there were only two possible states, I can now check and see what I'd want to do in my new third case. This is better than starting with nullability, because if I don't know what NULL means or why I'd have it, it's really difficult to know what my code should do when it encounters it. You could always throw errors, but if you do that you have to ensure you clean all of that up before you start actually using NULL. This is fundamentally about data modelling though. Talking in this abstract sense is not useful. What I feel you're failing to make an adequate case for is why a field like the one described in the linked article needs a state other than "set" or "unset". Just putting a nullable timestamp is a nearly free upgrade from putting a non nullable boolean, which is what I would've done anyways. If I want to add more states, I want explicit data model changes, that result in explicit test and type checking errors, that highlight wherever I need to update my code's state management to account for new states. Additional note: particular to SQL, removing not NULL constraints is basically free from the database end as far as I can tell. In addition, NULL has specific behaviors, especially w.r.t. JOINs, so I would NOT want to use NULL to model a tri-state.
- Twisell 4y ago
  It always depend on the use case you are trying to model. My statement was primarily a counterpoint to OP "you might at well timestamp it". I guess it is a valid take on the specific use case of publication date he mention. The problem is generalizing from that. For user inputed data you might NOT want to coerce a default state when the user genuinely can't answer otherwise you might collect garbage because i can guarantee that the user will input random answer to unblock itself. Of course you can define enum, but the NULL value already cover 80% of use case when used properly (as far as database are concerned). NOT null might be a useful DB constraint (especially for ids participating columns). But you just have to read PostgreSQL release notes to notice that null handling is actually a hot topic which always call for more fine tuned settings to accommodate different workflow. People use theses features a lot. See : https://news.ycombinator.com/item?id=32053293 https://news.ycombinator.com/item?id=32053293 As far as JOIN are concerned bad NULL handling is almost always coming from a bad understanding of OUTER JOIN mechanics. Anyhow it's an endless debate and I guess we won't be able to settle the NULL Schism tonight! See : https://news.ycombinator.com/item?id=18588239 https://news.ycombinator.com/item?id=18588239 So maybe let us end with this beautiful quote from last link top comment by @porpoisely NULL exists to solve particular problems in computer science. It can also cause a lot of problems. You can argue it's the best solution and worst mistake depending on the situation.

jchw 4y ago

What I don't get is, why would I want there to be a third state in this boolean example? You're saying it could be useful. That's true, it could. However, we're talking about something that today is already just a boolean, such as "is_published." Even if you can envision a reason why you might want a third unknown state for "is_published", if you write a bunch of code that treats it as a boolean, without knowing what the null state might mean, you are not solving a problem. You're just modelling your data slightly less precisely. Saving a date is saving known, useful state, even if you don't need it yet. Using a nullable type for a field that can't yet be null is just adding an additional unknown state for no reason. When using Go and sqlc, what would happen is I would use a NOT NULL field for the boolean, because I'm modelling a boolean, not a tri-state. If I was modelling something where I had three states I wanted to handle, I would use an enum instead. But even if I wanted to use NULL instead of an enum that explicitly specifies the meaning of the third state, I'd rather start as NOT NULL. Why? Because then, if I ever want to remove the constraint, I can. And when I do, sqlc will helpfully change the type of the field from a raw boolean to a nullable boolean wrapper. And doing this will break the existing code. This is good. Why? Because in every place where I assumed there were only two possible states, I can now check and see what I'd want to do in my new third case. This is better than starting with nullability, because if I don't know what NULL means or why I'd have it, it's really difficult to know what my code should do when it encounters it. You could always throw errors, but if you do that you have to ensure you clean all of that up before you start actually using NULL. This is fundamentally about data modelling though. Talking in this abstract sense is not useful. What I feel you're failing to make an adequate case for is why a field like the one described in the linked article needs a state other than "set" or "unset". Just putting a nullable timestamp is a nearly free upgrade from putting a non nullable boolean, which is what I would've done anyways. If I want to add more states, I want explicit data model changes, that result in explicit test and type checking errors, that highlight wherever I need to update my code's state management to account for new states. Additional note: particular to SQL, removing not NULL constraints is basically free from the database end as far as I can tell. In addition, NULL has specific behaviors, especially w.r.t. JOINs, so I would NOT want to use NULL to model a tri-state.

Twisell 4y ago
It always depend on the use case you are trying to model. My statement was primarily a counterpoint to OP "you might at well timestamp it". I guess it is a valid take on the specific use case of publication date he mention. The problem is generalizing from that. For user inputed data you might NOT want to coerce a default state when the user genuinely can't answer otherwise you might collect garbage because i can guarantee that the user will input random answer to unblock itself. Of course you can define enum, but the NULL value already cover 80% of use case when used properly (as far as database are concerned). NOT null might be a useful DB constraint (especially for ids participating columns). But you just have to read PostgreSQL release notes to notice that null handling is actually a hot topic which always call for more fine tuned settings to accommodate different workflow. People use theses features a lot. See : https://news.ycombinator.com/item?id=32053293 https://news.ycombinator.com/item?id=32053293 As far as JOIN are concerned bad NULL handling is almost always coming from a bad understanding of OUTER JOIN mechanics. Anyhow it's an endless debate and I guess we won't be able to settle the NULL Schism tonight! See : https://news.ycombinator.com/item?id=18588239 https://news.ycombinator.com/item?id=18588239 So maybe let us end with this beautiful quote from last link top comment by @porpoisely NULL exists to solve particular problems in computer science. It can also cause a lot of problems. You can argue it's the best solution and worst mistake depending on the situation.