One-to-one relationship or NULL field for optional column?

Question

I am working on a database design and would like to store user addresses. Consider the following: Many times when you register your address with a website there is 'address line 1' and 'address line 2'.

Since 'address line 2' is optional what is the best practice for storing it?

Would you want to use a single table and allow 'address line 2' to be NULL or would you want to create a separate table with a one-to-one relationship?

FYI using MySQL for my database.

Answer 1

None of the Above - A non-nullable field that defaults to a single-space. Where you can, avoid the headaches of having to coalesce your nullable fields. to get intuitive Boolean results

The real reason why it is wrong to create a separate table, in this instance, is that the address label only has room for two address lines. Therefore store the two address lines in the main table.

In other circumstances the maximum number of possible values is truly unknown, so a separate table is (at least theoretically, there can be overriding considerations of practicality) correct.

Update: Note that in this example, the value of all spaces is already a special value that must be handled specially - by omission on the printed address label (or other display medium). Adding an additional special value, NULL, with the identical semantics and handling on the printed lable is wasteful ad unnecessary.

Answer 2

I agree with Pieter Geerkens, about using the main table.

Yet, if your design requires meta information about that extra second line, such as a name label or type-of-second-line such as an enum of "floornum","appartmentnum","buildingnum", that'd be a case for using a second table.

.. but otherwise, store in main table. And if you do, my answer is use a NULL value. That is the standard default when absolutely nothing is stored