title: Why would you store a delimited list in a SQL text column?
body: <p>I have to maintain an application that has a lot of columns that are of the text data type, with multiple values inserted into them delimited with commas, slashes or sometimes even the pipe (|) character.  I'm trying to figure out why on Earth you would ever want to do this.</p>
  
  <p>For an example, an orders table has a column called details that contains information like:</p>
  
  <pre><code>2x #ABC-12345  Widget, Black: $24.99 /4x #ABC-12344 Widget, Blue: $23.50
  </code></pre>
  
  <p>where the <code>/</code> separates the line items; there is VBScript code which reads the value from a recordset and parses it out in a <code>For</code> loop for display using something like (and this is pretty much exactly how the code reads, variable names and all) <code>arydtls = split(rstmp("details"), "/")</code>.  This method is repeated throughout the code for various tables.</p>
  
  <p>It seems to me like it would be 100x better (not to mention easier to work with) to just have the details in a separate table and link back to it (funnily enough, for Orders it <strong>does</strong> do this, but the data doesn't always match the details text field because the OrderDetail table is updated in code; the details field is treated as read-only in the application).</p>
  
  <p>Did my predecessor know something I didn't, or am I right to be saying "WTF?!!" when I look at this schema?  It seems like it's insanely inefficient and difficult to maintain like this, and it makes running reports extra difficult because the data I need could be contained in text fields OR it might be in one of a dozen tables that have similar information and are used in different parts of the application.</p>