I'll use a simple example from the financial sector to illustrate the issue. Specifically, a table that describes financial instruments (only stocks, futures and options).
I'm going to simplify the table to make the example as small and easy as possible (i.e. it's not realistic).
'Table v1.0' columns: name, term, type.
'name' would be one of stock, future or option.
'term' is a date. This is always Null for stocks, as it actually only applies to the other two.
'type' is Put or Call for options and Null for the others.
Note that 'name' is not a candidate key (it would be for stock, but not for futures and options). 'term' depends on 'name' (it's Null for stock), 'type' depends on 'name' and 'term' (as it only applies to options).
This is definitely not 3rdN as far as I can tell.
'Table v2.0' columns: name, term.
'name' would be one of stock, future, call or put.
'term' is the same as in 1.0.
This respects 1stN only because I shortened 'Call Option' and 'Put Option', and still has the 3rdN problem on 'term'.
Apparently the specifications of these instruments are incompatible and I should have a table for each of them (even though the stock one would have only 1 entry). This would be annoying as other tables would use the row id from this table as foreign key to link information about, say, a trade. If I split in 3 tables, I would need a 4th in order to detect which of the 3 to access to link trade and instrument.
Would it be that bad to stick with design 1.0 (considering that data correctness for this table is already guaranteed before insertion)? Is there a pattern to use in these cases that avoids having a table for each kind of instrument?