4 ms·
Nullable is being set by accident? Nullable values are an “archaic” option? Nullable is different than empty. Consider a contrived example where I’ve extended
by marcc 7y ago
Nullable is being set by accident? Nullable values are an “archaic” option?
Nullable is different than empty. Consider a contrived example where I’ve extended a user table to include a new column “fullName”. By making this a nullable column, I can determine that a user has not set this value. But maybe the user sets it to an empty string. The API might want to know this and handle it differently — prompting for a value if it’s null, but ignoring “” as “this is what the user wanted to set”.
Without nullable values, I would have to create bit values in a lot of tables indicating if the value was ever set.
Another example is time stamps. Databases have timestamps for createdAt and updatedAt often. There are people who set updatedAt when they set createdAt, but there are people who don’t. I’m in the later camp. If an object was created but never updated, I want to be able to know that. How would I do this if the updatedAt column is not nullable? I’d either have to set a bit field that it has been updated, which feels suboptimal, or I could set it to something like “time.Empty{}”, which is a magic number tied to the programming language? There are plenty of other solutions here, sure, but what’s wrong with nullable fields?
- pushpop 7y agoFirst example: why would you want to handle null and a zero length string differently in that case? That seems a little contrived to me because in both instances the user has elected not to enter that information. Re your timestamp example, just compare if created is the same as updated. That’s a really easy problem to solve. Though I have seen other scenarios when a timestamp needed to by nullable so I agree there will be edge cases. I agree that null does have its uses (it’s basically essential for outer joins) but most of the time people think they need null they don’t actually need it. Plus it frequently it causes hidden faults, so it’s usually best to avoid allowing nullable fields unless you’re sure there isn’t any other easy way.
- marcc 7y agoThe tool referenced here is for go, a programming language that has pointers. Pointers can be nil too, which is useful for many of the same reasons that null database columns are useful. There is a difference between uninitialized, initialized with default values, and a custom value. You may not have this use case you in your application, but I do. I have real scenarios that need to know if a value is initialized or not. I agree that I could solve this in other ways, but nullable columns just aren’t an archaic database option just because you can implement the same behavior in the application code or by rewriting the SQL query.
- pushpop 7y agoI’ve had a great deal of experience with Go and there are many who’d argue that nil pointers in there is a mistake as well (though I’ve personally never had an issue - but I do write pretty robust tests too). I have ran into some instances when I have needed nullable fields in relational databases as well. I’m not claiming null shouldn’t exist. My point was that they should be an opt in rather than opt out because they cause more problems than generally they solve.
- pxue 7y agoRegarding timestamp, I often have a soft delete timestamp 'deletedAt' in which it must have a null default value. I think this is a perfectly acceptable usecase of db null values
- pushpop 7y agoIt is though I’d be more tempted to have that as it’s own table with more data attached such as the UID who deleted it, reason (if application supported that), etc. That way you can have more detail on “deleted” records without having to null several fields nor increase your table size for your main table with lots of “deleted” metadata when typically records wouldn’t be marked as “deleted”. Though “deleted” isn’t really the right term here either because you’re not really deleting data, just removing it from view.