> Well, in an ideal case, yes, you would be doing it like that.
I mean, it'd be either that specific case or a bug. And if it's a bug, then the check constraint as described previously would catch it and prevent it entirely.
Another option, though, would be to not even bother with a separate field for "final"; if the only two states are "draft" and "final", and finality is conditional on there being an invoice number, then the application logic can be greatly simplified:
SELECT CASE
WHEN invoice_number IS NULL THEN 'Draft'
ELSE 'Final'
END AS state
FROM invoices WHERE -- yadda yadda yadda
Most databases can cache this as a computed value column on the table itself or in a view or what have you.
> Draft invoices do not have an invoice number, since they don't really exist anywhere.
Sure they do: they exist in your database. Unless your company has a strict rule about invoice numbers always being sequential with no gaps (and that'd be pretty darn strict, in my experience), you might as well pre-assign it.
I mean, it'd be either that specific case or a bug. And if it's a bug, then the check constraint as described previously would catch it and prevent it entirely.
Another option, though, would be to not even bother with a separate field for "final"; if the only two states are "draft" and "final", and finality is conditional on there being an invoice number, then the application logic can be greatly simplified:
Most databases can cache this as a computed value column on the table itself or in a view or what have you.> Draft invoices do not have an invoice number, since they don't really exist anywhere.
Sure they do: they exist in your database. Unless your company has a strict rule about invoice numbers always being sequential with no gaps (and that'd be pretty darn strict, in my experience), you might as well pre-assign it.