title: What is the best MySQL table structure.. or the best way to query my existing structure?
body: <p>I am working on a website where users have core data about their profile (user_name, email, preferences, etc) as well as arbitrary data that could change on the fly. Not all users would necessarily have a need for all data fields. Thus on my tblUsers MySQL table, I don't want to add a bunch of columns that might only be practical to a small percentage of users.</p>
  
  <p>The way I imagined would be to create a second table, with the following columns:
  UID INT, dataType TINYINT, dataValue INT</p>
  
  <p>Basically UID would point to the User's ID in the users table (tlbUsers) and the dataType would point to an ID in a list (another table) of dataTypes, such as "Age", "Favorite Color", "Points", etc.</p>
  
  <p>The problem is, when I say:
  "SELECT * FROM tblUsers, tblData WHERE UID=ID"
  I get several rows stacked (which works well enough...)
  But I can't figure out how to write a query which takes the tblData information into consideration.</p>
  
  <p>For example, let's say I want to select all users who are 21 and have a score between 400-500.</p>
  
  <p>If they were actual columns, I would say:</p>
  
  <pre><code>SELECT * FROM tblUsers, tblData, WHERE UID=ID AND dataAge = 21 AND dataScore &gt;= 400 AND dataScore &lt;= 500
  </code></pre>
  
  <p>However, I can't do this because dataAge and dataScore aren't columns - they are rows in the dataTable like such:</p>
  
  <pre><code>    UID  dataType  dataValue
      35   1         21         //user #35's age (dataType 1)
      35   2         467        //user #35's score (dataType 2)
      49   1         21
      49   2         491
  </code></pre>
  
  <p>I cannot predict what dataTypes will be required in the future.
  Users could arbitrarily add dataTypes about themselves, and not all users will have all possible data types at once.</p>
  
  <p>I imagine also using another table, for text data, with the same format, UID, dataType, dataString.</p>
  
  <p>Lets say I write</p>
  
  <pre><code>SELECT * FROM tblUsers, tlbData, WHERE UID=ID AND dataType=1 AND dataValue=21 AND dataType=2 AND dataValue &gt;= 400 AND dataValue &lt;= 500
  </code></pre>
  
  <p>because I want to compare both Age and Score, both dataType and dataValue are used ambiguously in the same call...</p>
  
  <p>My question: What's the best table structure for my needs OR how can I properly query my current set up?</p>