title: Shared Purpose tables in database design ( how would *you* implement a question/answer facility )
body: <p>I have long considered the design of a database that involves shared table purposes to be somewhat a trait of smelly code, and progressively increasing proliferation of smelly-code related problems.</p>
  
  <p>By this I mean, people over-normalizing, using 1 table where 2 tables could be more logical, people who've just discovered what normalizing is and overuse it to the point where they virtually layer a database on a database, or trying to use hierarchical data naively to solve all their problems. </p>
  
  <p>The question is, when you have 2 sets of data, which appear to be the same, but they have a different purpose, do you use the same table to represent it? When do you know when it is a good idea and a bad idea to use this? </p>
  
  <p>I've always been of the mind that needless self-referencing tables or structures that involved any table being used twice in the same query was a very dangerous predicament, both in design, and in long term performance and ease of future improvement.</p>
  
  <p>That was of course, until I saw a thing or two in the RSS feeds for SO here. Now I'm not going onto a meta discussion about how SO works, but there appears to be an implicit design consideration here that challenged my thinking and want to glean a more cohesive answer on that style of logic. </p>
  
  <p>You'll notice the generic question has the format: </p>
  
  <pre><code> /question/1234/stuff-here-that-s-safe-to-leave-out
  </code></pre>
  
  <p>And I generally assumed that this implied that questions were numerically ordered somewhere. </p>
  
  <p>But here is what stumped me:
  I'll take for example <a href="https://stackoverflow.com/questions/316210">question 316210</a>. If you look at the <a href="https://stackoverflow.com/feeds/question/316210">rss feed</a> for this question, you will note there is a entry for the question, and a series of answer entries, which are functionally identical for the question entry except for a few minor differences. Now note in the answer entries also have link references, ... to questions, not the same question however, but to different questions, such as <a href="https://stackoverflow.com/questions/316218/">question 316218</a>, which , when visited, redirects you back to the original question. </p>
  
  <p>Now I'm not interested in <em>how</em> they implement that in the code, the problem is that you have here, questions and answers appear to be sharing the same table ( hence the sequential question ID's ) , and when users refer to an answer ID, you have to first query the database, and then go "hey!, oops!, that's not a question!" and then proceed to do a <em>second</em> query to find out the parent of that question ( in the same table ) and then redirect you to the actual question page, not to mention all the hullabaloo required with self joining queries ( which I've always considered filthy ) and conditionals all over the place to tune behavior. </p>
  
  <h3>Less Digressing, the real problem</h3>
  
  <p>The problem is, here you have 2 sets of data sharing the same table, and sure, this data is superficially similar, for now at least, but there looks like there is just so much <strong>technical debt</strong> involved.  </p>
  
  <p>The long term considerations involved with implementing new features that can apply to questions and not answers and vice versa, not to mention avoiding one being interpreted as another in some obscure corner. You can't add a new column for use in one application set without having to consider the resulting effects in another. </p>
  
  <p>Sure, there is a minor benefit from using the singular table and that's when you <em>are</em> making a feature that is shared between facets you only have to code it once, but this could be just as easily represented by using an ancestor class of common methods, and child classes that bind to the specific tables for the difference cases. So at least that way, adding a new feature has no follow-on implications for the other scenario. </p>
  
  <p>Now I've encountered this sort of problem in many places before, sure, but SO is the most easy example to point out. </p>
  
  <p>When you implement your databases like this, do you share the table, or do you fork?</p>
  
  <p>When, and why?</p>