1

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.

4
  • 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?
    – user44761
    Aug 16, 2013 at 20:55
  • Added a clarification Aug 16, 2013 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, 2013 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?
    – NoChance
    Dec 15, 2013 at 13:29

2 Answers 2

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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