4 ms·
What's the alternative, an empty string? IMO the problem (at least in this case) is not NULL in the DB, but NULL at the application level. If NULL is some sor
by kaoD 3y ago
What's the alternative, an empty string?
IMO the problem (at least in this case) is not NULL in the DB, but NULL at the application level.
If NULL is some sort of Maybe monad and you're forced to deal with it, well, you're forced to deal with it, think about it, etc.
Empty string, whatever NULL string is in your language of choice, or some sort of sigil value you invent... not much of a difference.
- Tempest1981 3y agoA separate Boolean column?
- kaoD 3y agoCan't wait to have a NULL url and has_url TRUE. Might or might not be based in production data I deal with on a daily basis.
- semiquaver 3y agoJoins are cheap. Wide tables are often a sign that a data-model is a bit too CRUDdy. Foreign key relationships often do a much better job modeling optionality/cardinality in relational systems. In this case, a `user_uris` table with non-nullable columns and a unique constraint on `user_id` is the first option that comes to mind.
- traxys 3y agoI had a situation where I'm not really sure I could have used something else than null: I need a value in one of two columns exactly (meaning one is NULL and the other not). You can build a constraint to check that if it's in the same table, but across tables it seems to be a bit more complex right ?
- snmx999 3y agoYou could have the first column indicate the type of value and the second column the value. If you now have your columns named "a" and "b" you could have the first column named "type" only allowing values "a" or "b" and the second column would be "value" where the actual value is stored.
- kaoD 3y agoFor a table with a single column (plus FK), isn't LEFT JOIN isomorphic to just having a NULLABLE column (and much better at data locality)? It might prevent error if you only rely on INNER JOIN but that's rarely the case at least for me (you often want to access the record anyways). Much safer to deal with the NULL at the application level.
- ngc248 3y agoAt scale ... it is better to use CQRS ... sou you have a transaction model which is fully normalized and a read nly model which is wide, if you really want to use a RDBMS.
- int_19h 3y agoThe problem is precisely that NULL is not some sort of Maybe monad, but people keep trying to use it as such. It's a lot like using NaN as a sentinel value for floats - sure, you can do that, but when something goes wrong, instead of an error at the point where the problem is, you end up dealing with a mysterious NULL somewhere way down the line. And that's the best case - the worst is that you get wrong query results because of the way NULL comparisons work. An empty string is better as a sentinel value because at least this doesn't have the weird "unknown value" semantics that NULL does. But if you really want the same level of explicitness and safety as an option type, the theoretically proper way to do this in relational model is to put the strings themselves in a separate table in a 1:N (where N is 0 or 1) relationship with the primary table.
- vbezhenar 3y agoWith empty string there's gotcha: Oracle treats empty string and null as the same values. If you really need sentinel value, generate UUID.
- mst 3y agoI believe that's a nasty implementation detail of Oracle and not a problem for anybody using anything else. A generated UUID, if possible, is probably much more clear because it will only be there if it was inserted very deliberately. But at that point honestly I'd usually prefer breaking the field out into its own table.
- resonious 3y agoIt looks to me like using empty string would not have prevented the bug in the article. If their language had maybes, they might be able to prevent this bug by having a function type signature where uri is concrete. And most langs with maybes will automatically turn external nulls into maybes.
- tedunangst 3y agoIn this specific case, all the local users could have had URLs in the database instead of NULL (or empty string), which would have prevented them from merging.
- deleted 3y ago[deleted]
- mananaysiempre 3y ago> What's the alternative, an empty string? Yes! NULL is relational for “don’t know”, and SQL is (mostly, with varying degrees of success) designed to treat is as such. That’s why NULL=anything is NULL and not e.g. false (and IMO it’s a bit of a misfeature that queries that branch on a NULL don’t crash, although it’s still better than the IEEE 754 NaN=anything outright evaluating to false). If the value is funky but you do know it, then store a funky value, not NULL.
- TylerE 3y agoBut a known value of empty string is a fairly commonplace thing, and I have never wanted to treat that as null.
- mananaysiempre 3y agoI’m not sure what to say here. ... Yes? If you’re referring to the Oracle behaviour where '' IS NULL, well, the rude way of putting it is that Oracle is doing a stupid. The more polite way of putting it is that Oracle is absolutely ancient and these parts probably existed long before people had the theory developed well enough to recognize which things make sense and which don’t, and now Oracle’s backward-compat-driven livelihood depends on not recognizing that it’s making no sense there. Either way, if this matters to you, you’re stuck and will have to work around this particular wart.