Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
1  
Either you have the feeling that at some point you will need 1..n - and you should keep your design - or not - and your teammates are correct. Besides, I don't buy your "makes queries easier" argument as is. Could you clarify à bit? –  Tibo Aug 16 '13 at 20:55
    
Added a clarification –  Kenneth Cochran Aug 16 '13 at 21:19
8  
Don't tie your UI to the database. There's plenty of code you can put between them. –  JeffO Aug 17 '13 at 2:43
    
I disagree with this approach in general. Separating optional fields will not solve the different null interpretation problem. If you have 100 tables with optional columns will you be adding another 100 tables of optional columns? –  Emmad Kareem Dec 15 '13 at 13:29

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.