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



My questions:

- 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.

Thanks kids!!!

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:

http://asktom.oracle.com/pls/ask/f?p...:9103542139079

http://asktom.oracle.com/pls/ask/f?p...D:469621337269

For other DBMSs, different criteria may apply.

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

pokeynyc,

Your'e understanding is incorrect. Normalisation and performance have nothing in common. Normalisation is logical, performance is physical.

A one-to-one constraint, AFAIK, is impossible to declare declaratively in DB2, Oracle and SQL Server. But I suspect you mean One-to 0 or One.

Follow you functional dependencies to determine if this type of constraint is needed.