title: Storing Secondary Data Based on Primary Selection
body: <p>Generically, I'm wondering how I would store secondary data based on the type of primary data.</p>
  
  <p>Here's the real-world situation.  When adding "students" to my database, I want to start their referral.  This will be done by referencing a table called "referrer" that stores a list of options that will appear in a drop-down box, as such:</p>
  
  <pre><code>+-------------+---------------+
  | referrer_id | referrer      |
  +-------------+---------------+
  |           1 |       Student |
  |           2 | Search Engine |
  |           3 |            Ad |
  +-------------+---------------+
  </code></pre>
  
  <p>So in the student's table, I'll store the referrer_id -- Easy enough.</p>
  
  <p>Based on the selection, I'd like the user to be able to enter secondary information.  For instance, if they choose "Student" from the list, a second drop-down box will appear presenting them with a list of existing students so they can specify which student was the referrer, and I would want to store the student_id.  If they chose either "Search Engine", I would present them with a text box where they can enter the name of the search engine, and I would store that text.</p>
  
  <p>So my question is, how should me "students" table be structured in order to hold this information, since sometimes I'll be storing a student_id, or the text entered in a box, or... ?  My initial thought is to create additional columns in the students table to hold the various possibilities, but that seems both messy and not scalable, so I don't care for it.  I'm wondering if an additional cross-reference table would be warranted here, that would hold the student_id, the referrer_id, and then a value (such as the referring student_id, or the value entered into the text box).  Or perhaps instead of "value", I could have "value_id", "value_text", and so on for different data type possibilities.  The value of referrer_id in this table would tell me which column contains the data I want.  While this seems cleaner than expanding out the students table, it does still seem a bit messy.</p>