title: The Wide Vs Tall Table - benefits of each
body: <p>I want to create a table for UserPreferences.  There is a finite set of user preferences for each user. So this could be modeled in two ways:</p>
  
  <ol>
  <li>A wide table: UserPreferenceByUser(id int, userid int, preference1 int,
  preference2 int)</li>
  <li>A tall table: UserPreferenceByCode(id int, userid int, code nvarchar(25),
      value int)</li>
  </ol>
  
  <p>The speed factor is not important as they will be retrieved once at the start of the app.</p>
  
  <p>The pros/cons of 1:</p>
  
  <ul>
  <li>On the client, one object holds all preferences. (Easier to use on the client)</li>
  <li>However, every time a preference is added, then the table must be modified and the app updated. </li>
  <li>The default preference value can be put in the default value
  of the field.</li>
  </ul>
  
  <p>The pros/cons of 2:</p>
  
  <ul>
  <li>On the client, an array holds on the preferences. (More difficult to use)</li>
  <li>No data model changes necessary if a new preference is added. However, when new users are added, there are no default preferences. </li>
  <li>If a preference is looked up and not found, a new record must be added, looking up what the default preference value is in another table (DefaultUserPreferences).</li>
  </ul>
  
  <p>Am I missing any pros or cons in the above?</p>
  
  <p>It seems to me in looking this over the ideal solution would be storing the data as UserPreferenceByCode, but then returning a pivoted set of data so it appears on the client as UserPreferenceByUser.</p>