That was probably not the best example. Here is another example, let’s say for Credit Card payments:
I will require the billing address for verification (cannot be null). The shipping address is often the same as the billing address but not always.
I could set up my tables this way:
customer
customer_id PK, name not null, billing_address not null
customer_shipping
customer_id PK, shipping_address not null
And customer_shipping would have no rows if shipping_address is same as billing_address
But in most cases I would live with the NULL values instead of having a separate table - only a fool will have a fully normalized database.
My example above would probably look like this in reality:
customer
customer_id PK, name not null, billing_address_id not null (FK to address table), shipping_address_id null (FK to address table)
address:
address_id PK, street, city, state_or_province, postal_code, country not null
The simplest method:
Transaction Table
Billing Address
Shipping Address (application fills in shipping address with billing info if user indicates they are the same, otherwise user must key it)
Any “trickiness” should get resolved once so all down stream operations are less complex, instead of having to re-resolve the shipping address in every application, query, etc. down the road.
The hair colour thing is a bit of a red herring, because in practical applications, ‘no hair’ would be a not entirely unreasonable option to include, in the same way that you might have ‘atheist’, ‘no religion’, ‘unknown’ and ‘declined to answer’ in a field intended to store the religious persuasions of the person described. It doesn’t matter that this is an offence against absolute consistency of class in the described characteristic - in the real world, this happens all the time - you either accept and handle nulls, or you make one or more of your list items suitable as a replacement for null.
Dates are a different animal though, because you can’t put ‘n/a’ in a date field, and putting in some fictional date such as 01/01/1900 is guaranteed to eventually come back to bite you on the arse - you’ll end up accidentally congratulating a customer on reaching their 111th birthday, when in fact they’re only 31 and you just didn’t know their DOB.
And storing the date as text is also asking for trouble, because you suddenly lose strong validation on it, and you’ll end up storing a mixture of date formats, and you’ll have trouble calculating the difference between two dates.
You have to either handle nulls, or do something else such as having an additional boolean field to indicate whether the date is known, and test for that when you’re doing anything with dates. Except that’s much harder and considerably less reliable than just dealing with a single column that may contain nulls.
If there is some tricky way of resolving the shipping address, that can be built into a view, and the SELECT statements in the application can select from the view.
True, but what exactly is being gained by not explicitly storing the shipping address? There is a definite gain if you do explicitly store it and that is reduced complexity and reduced knowledge required to access the information (don’t need to remember to use the view) and thus fewer errors later on.
What is gained is avoiding the duplication of information - if the billing address is the same as the shipping address, and they are both stored in the database, then whenever you change one you have to remember to change the other.