6 ms·

Avoid NULLs in SQL schemas. Embrace NULLs in outer joins.

• wvenable 11y ago
  It's impractical in real life to avoid NULLs in SQL schema. Just embrace NULLs altogether; they aren't hard to understand or work with.
  • Retra 11y ago
    They are hard to understand. You don't know if they represent missing data, invalid data, or valid data that is null-valued, so you never know how to handle it.
    • mirimir 11y ago
      They're easy to handle. For any column with nulls, you just need to deal with them explicitly.
      • Retra 11y ago
        And that's your standard for "easy to handle?"
        • mirimir 11y ago
          Yes. What's wrong with that? Let's say that I have a bunch of contact information, from multiple sources, to synthesize. Sources include guest lists, registration forms, notes from agents, and other hand written information. Some of the information is illegible, pages are ripped and damaged, etc. So let's consider email addresses. In some of our sources, there's no place to enter them, but we know that most people have at least one. The appropriate value for those email addresses is "NULL". That's also appropriate for email addresses in other sources that are illegible. Conversely, in reliable sources, missing email address means either that there is none, or that it shouldn't be used. In those cases, it would be appropriate to leave the column empty, or use "NONE", or even "nobody@dev.null". That's how I learned to handle "NULL", anyway.
          • Retra 11y ago
            What's wrong with it is that it's cherry-picking. "Easy to handle" means you don't have to manually interpret the values every time you deal with them. That's the default! That's the hardest a thing gets to handle... You're talking about an innately fragile process. This algorithm is not general, and it's not generalizable, and thus it represents an upper limit on the effectiveness of our ability to write good software. You've got a method for solving multiple problems here; you gave me one example. Now give me an implementation of that algorithm, and you'll find it doesn't bother to employ nulls at all. You're just mapping other concepts onto null because that's how you imagine a database to work. You're familiar with a hammer, so you're employing nails all the time. >Conversely, in reliable sources, missing email address means either that there is none, or that it shouldn't be used. In those cases, it would be appropriate to leave the column empty, or use "NONE", or even "nobody@dev.null". A properly normalized database would put emails in another table and reference them using a key. You'll have no empty columns. You'll know there is no email because there is no value, not because there's a NULL value. If you want to send emails, you do a join on these two tables and get a list of people who have emails. This is the proper way to model such a domain. In the mean time, you'll just have a fragile model. Then you'll use that fragile model to make fragile decisions, and your database will be completely useless in 40 years. (Granted, your DB implementation might combine these tables as an optimization, but then the optimizer has a clean notion of what NULL means across your entire schema, and you shouldn't be exposed to it at all.)
            • mirimir 11y ago
              I'm not talking about databases. I'm talking about processing data that will end up in databases. The final databases will for sure be normalized.
            • wvenable 11y ago
              > A properly normalized database would put emails in another table and reference them using a key. You'll have no empty columns. You'll know there is no email because there is no value, not because there's a NULL value. A table with 2 columns (key, value) is logically equivalent to a nullable column in the parent table. There are times when a separate table is a good solution. But there are also times, for example if you have a lot of optional columns that cannot be grouped into sets, that such a thing would be massively over-complicated, space wasting, and poor performing.
    • wvenable 11y ago
      That doesn't make them hard to understand. Every column requires some context to understand adding NULLs changes very little.
      • Retra 11y ago
        The context of a column can't tell you why a given row is null. For instance, if you're looking at a middle_name field in a database and you see NULL, you have no idea what that means. It doesn't model the thing the scheme was meant to model, it only models the system's ignorance about the world.
        • wvenable 11y ago
          If I also have column that is called "Active" that is True or False what does that mean? I have to look somewhere else for that information. You seem to want to ascribe more pain to nulls than makes sense relative to other values. NULL means I couldn't store a valid value in that column. Why? Well that depends on the system, just like the contents of every other column.
          • Retra 11y ago
            What it means is that you have an awfully named column. >NULL means I couldn't store a value in that column. Why? Well that depends on the system, just like the contents of every other column. No, it means you stored a NULL value in that column. Why you did that rather than throwing an error or generating a more informative value is a mystery. And it is not made clearer by changing the name of your column. And someone writing code to handle that data can't know how to handle it, because they have no clue what went wrong, or even if it is wrong.
            • wvenable 11y ago
              > Why you did that rather than throwing an error or generating a more informative value is a mystery. Why would you think it's automatically an error or that a more informative value could exist? I might have a form with yes/no or date answers and if a question isn't required and the user doesn't provide an answer then it's stored as NULL. Either way the meaning is obvious, it's read and written perfectly fine, and it's not an error. I don't understand the implication that storing NULL means something went wrong? That's not at all what it means to me. I'm going to assume something about your response: you seem to conflating the concept of stored null value in database vs. returning a null reference or pointer in C.
  • dragonwriter 11y ago
    It's not impractical; it is occasionally undesirable for performance or otger reasons to normalize to a level that would avoid all nulls in base tables, but more often than not nulls are permitted because of poor data modeling. Nullable columns are not always wrong, but they are a design smell.