It's not a hard and fast rule, but in your example you can solve this in the model without needing NULLs. A table called ORDERS records the orders, SUPPLIERS lists your suppliers, and a table SENT_TO_SUPPLIER links ORDERS to SUPPLIERS, and you don't add a row into that table until the order has been sent.

What about this, let's say I have two electricity meters. They have bunch of fields (active power, reactive power, voltage, current, and all that times 3 (one such measurement for each phase)), but one kind of meter can measure distortions, the other can not.

I would make the distortion columns nullable for the pratical reason that you either have to duplicate all the work for querying the same properties from the meters (for example a dashboard that shows voltages only). If you want to support both types of meters in the same dashboard you would have to do a UNION type query (and not ever forget to do that!) if you store the measurements in a different table.

It's possible to handle these kinds of fields (at most one value) with separate tables and foreign keys. It has advantages around 'proving correctness'. But without extra tooling it has significant usability, and perhaps performance disadvantages. I wonder if you can get around some of this using virtual tables.

I come at this with little experience with SQL, and having worked a bit on ampersand[1], a tool where you declare the structure of your data, and some invariants, and the tool will create a schema for your database, and some automatic checking to ensure your invariants are upheld.

[1] https://ampersandtarski.gitbook.io/documentation/

Rather than putting the two types of meters in two different tables and doing an error-prone UNION, you would put the distortion measurements in a different table joined to the first, and the meters that don't support it would simply not include entries in that table.

But that would still get you nulls in the result set, which is what we're trying to avoid?

Boolean capabilities of meter per every entry? Shouldn't impact size that much, easy to index. As a bonus, you can track failure of a sensor per each meter, and know when it first failed.

Oh yeah, solve complexity by introducing complexity squared.

Wouldn't a meter value that doesn't have distortions just have a value of 0 (that is, not null but just zero); the properties of a meter in this case belong somewhere else, either in a "meter properties" table if you have many meters with many properties that can change, or just hardcoded.

Alternatively, a key / value table, e.g. `meter_id, property, value`. But that isn't very optimal, works better for things like a shop product with many different properties per product.

It doesn't mean there are no distortions, it just means they are not measured. So I vastly prefer knowing there's no data than some placeholder that "simulates" no data

The submission process might deserve its own table with more detail than sent/not sent. Otherwise it's a straigtforward boolean, I don't see any use for NULL here.

Edit: Since it's a date, there are valid use cases for nullable timestamps. But only if there is no additional information attached to the event you're saving in the timestamp. Another complication with nullable timestamps is sorting by them, which you often do with timestamps. With nullable ones this can get messy.

You could create a new table that keeps track of the logistics of the product. If the product is sent to supplier, a new row appears in this table with the date. If it hasn't been sent yet, no row is present.

Handles the cases where an order had to be resent to the supplier better as well.

And when a supplier returns a shipment, and when new things happen that you haven’t thought of so a new column isn’t needed.

More careful thought about the processes and data that are to be captured.

In a simplistic view maybe NULL is really the correct choice; but is it? Does NULL represent the desired properties?

Another common pattern is the use of some sort of sentinel value, E.G. the largest possible value for a date field, or the smallest, might be used to indicate an unknown maximum or minimum that propagates.

A related pattern might be some sort of orders_suppliers table which would have a foreign key value; that might be NULL or it could use a sentinel value with a dummy supplier to indicate a special condition and an arbitrary number of inband subsets which can be their own distinct matches.

These all seem like fine examples of solutions that are worse than the problem they were meant to solve. A separate table will be harder and slower to query and will enable M2M relationships even when you don't want those. Sentinel values must be dealt with at every query to ensure they are always filtered out.

Thank you for pointing that out.

A better relationship would be if the order_suppliers table was the one with a relation to this table and there wasn't even a column.

A boolean column?

It keeps track of _when_, so timestamp.

If you care about ‘when’ you probably also care about resubmissions. Or metadata like the supplier’s response. Or who approved the submission etc. A separate table seems like a good solution.

What’s the null-replacing value in a timestamp field?