ok, my current understanding is that the only time 1:1 is permissable, given Normalization, is when you have performance concerns in mind. Either:
(1) You split one table into two in order to make table size more manageable, or
(2) You have a field that is sporadically filled. in order to not waste SPACE declaring a variable you'll not use, and the TIME it takes to search a field when you really (should) already know its NULL, you declare a child table using 1:1
- Is this understanding correct?
- what is the terminology for these situations?
- I'm working with a case of (2) above. So, i guess it becomes a question of how much waste you're willing to tolerate? Are there general guidelines for when to bother to declare a separate child table? I know that the answer will depend on % of records with NULL values in that field, and the data type of that field, but I mean specific rules of thumb or something.
Those are certainly reasons why it is sometimes done. I think any rules of thumb would have to be specific to the DBMS you are using. For example, Oracle wastes no space storing nulls in a column provided there are no non-null column values after it in the row. It is therefore good practice in Oracle to put all the NOT NULL columns before all the NULL columns. Oracle's guru Tom Kyte recommends against splitting tables on such performance/efficiency grounds unless they have 100s of columns:
another advantage of splitting a table into two, particularly if the second table split off contains a large amount of data, is that searches and scans on the first table should be faster, because there will be more rows per physical page on the drive