title: One big database versus many small databases
body: <p>My App deals with several similar datasets. That is, they are stored in the same tables, but different data. The user may create more datasets. In any case, these datasets are guaranteed to be disjunct. There will never be any data in one dataset linked somehow to data in another dataset.</p>
  
  <p>I was wondering, would it be better to have a dedicated database for each dataset instead of having all the data in one big database?</p>
  
  <p>I would expect lookup times to improve, if the user works on a smaller database. Is there a rule of thumb, how many entries a database (or table) can hold before I should worry about lookup times?</p>
  
  <p>One drawback I can think of is that opening a database creates some overhead. However, I don't expect the user to switch datasets frequently.</p>
  
  <p><strong>Consider this example:</strong></p>
  
  <p>The database contains tables for companies, clients, products and orders. Companies never share clients or products, thus companies are the disjunct datasets. However, all products, clients and orders are in just one big table (for each, respectively). </p>
  
  <p>Queries to the database might include:</p>
  
  <ul>
  <li>All orders for a particular client.</li>
  <li>All products a particular client has ordered.</li>
  <li>All clients who have ordered a particular product.</li>
  <li>etc.</li>
  </ul>
  
  <p>These queries have in common, that they will always be issued in the context of one single company. Yet since the database doesn't know about this logical partition, <em>all</em> clients, products and orders will be searched.</p>
  
  <p>If I were to have several databases, for each company one, my logical partition would be reflected and only the relevant data would be searched. I'm not sure of the overhead of having that many databases though.</p>
  
  <p>Since I'm new to database schema design, I want to throw this idea out there to see, if several databases really are a good idea or not.</p>
  
  <p><strong>Update:</strong></p>
  
  <p>In case this wasn't clear: the database will be on the Android Phone, not in the Cloud or something.</p>