What must I take into consideration when designing a UI around a 0..1:1 relationship?

Question

I'm designing the database schema for a new product feature. In my current design I have some related optional data. Rather than have nullable fields I have a separate table with a 0..1:1 relationship to the main table. I chose this design because the queries are simpler* if null data doesn't have to be taken into consideration.

The team lead pointed out to me that it will complicate data binding in the UI and suggested that I just use nullable fields. I am wondering what complications the optional table approach will introduce?

The obvious thing that comes to mind is the data aware controls can't bind to a closed data set so I'll need to either create a record on the fly when the user attempts to fill in the optional data or create the record at the same time as the main record, negating the purpose of have a separate table. Is there anything else I should be aware of?

Clarification

*Simpler, by which I refer to the dizzying amount of rules and ambiguities concerning null's behavior under different query scenarios in the SQL standard and the fact that no two vendors agree on which of these rules to implement.

Answer 1

I do not think you design simplfies anything as far as NULL handling is concerned. To use your second table you will need a "LEFT OUTER JOIN" to your main table which will return NULL values if there is no matching row in the second table. Or you will need to do the equivalent logic in your program code and have two separate code paths; one for "found a row" and one for "row not found". So what would be the net gain?

There may be other reasons to separate the data into another table but reducing the complexity of NULL handling is not one of them. What little code you save in checking for NULLs will be far exceeded by the complexity of maintaining a separate table.

Answer 2

Assuming you're doing this in C#, which you don't specify, you could set up the main data object like this:

public class MainObj
{
    public int ID { get; private set; }
    public string Name { get; set; }

    private SubdataObj _data;
    public int? OptionalID 
    { 
      get 
      { 
         if (_data == null) return null; 
         else return _data.OptionalID;
      }
      set
      {
         if (_data == null) _data = new SubdataObj();
         _data.OptionalID = value;
      }
    }
    class SubdataObj
    {
        public int OptionalID { get; set; }
    }
  }

Just populate _data on object load if you can, or leave it null otherwise. Note that OptionalID is a regular int, but we make it nullable when we expose it. Doing it this way means you don't have to do anything special for the UI, although it does mean more work when modifying the SubdataObj's backing table.