title: Table Structure for Managing a Collection with Org Table
body: <p>I have found org tables to be very powerful and useful. I feel like I have movement, table restructuring and basic formulas down fairly well. But I am having a difficult time wrapping my head around how I should structure this for tracking large collections. Not sure if I can do this in one table or if I need multiple tables.</p>
  
  <p>Say I have a business that buys and sells trading cards. There are baseball, basketball and football cards. I want to track purchase price, sale price, purchase date, sale date, average sale price, last sale price, quantity in stock, and item condition for every card sold or in stock.</p>
  
  <p>Is it possible to do this in a single table or do I need multiple tables? </p>
  
  <p>I'd like to track statistics such as:</p>
  
  <p>"What is the average price of all football cards sold in the last six months?"</p>
  
  <p>"In the last month, did I buy more basketball cards or baseball cards?</p>
  
  <p>And for a more lengthy example:</p>
  
  <p>"Last year I sold 4 Mickey Mantle cards. 2 in Mint condition, 1 in Excellent condition, 1 in Poor condition and 1 unsold. What percentage of Mint Mickey Mantle cards were sold last year?"</p>
  
  <p>To reiterate, in org-mode can all this be accomplished within a single table? How would it be structured if say, you knew Tops only made 2000 unique cards in a particular year, would table only contain 2000 rows? (plus the header)</p>
  
  <p>If it can't be accomplished in a single table, I'm just going to use a <code>postgres</code> database structured much <a href="https://stackoverflow.com/a/7590712/284194">like the one mentioned here</a>. I was really hoping there was a snazzy way to do this with org-table alone. But it looks like there are other ways to manipulate databases within emacs.</p>
  
  <p>Sorry if most of this sounds like a high school math problem with no code but I'm sure most people (at least here) know what a single org table with the mentioned columns and a finite set of rows would look like.</p>
  
  <p>Edit1: Can <a href="http://orgmode.org/manual/References.html" rel="nofollow noreferrer">org references</a> be used to link tables together to help get the results I'm looking for? </p>
  
  <p>Edit2: The reason why I thought this was possible in org-mode, was because I did not think a foreign key was necessary. <a href="https://stackoverflow.com/a/7590712/284194">Here</a> is a very similar example not using a foreign key. When reading about construction of spreadsheets in org-mode, foreign keys seemed to be the only obvious hurdle. Anyone have thoughts on this?</p>