title: How to design a database schema to support tagging with categories?
body: <p>I am trying to so something like <a href="https://stackoverflow.com/questions/48475/database-design-for-tagging">Database Design for Tagging</a>, except each of my tags are grouped into categories.</p>
  
  <p>For example, let's say I have a database about vehicles.  Let's say we actually don't know very much about vehicles, so we can't specify the columns all vehicles will have.  Therefore we shall "tag" vehicles with information.</p>
  
  <pre><code>1. manufacture: Mercedes
     model: SLK32 AMG
     convertible: hardtop
  
  2. manufacture: Ford
     model: GT90
     production phase: prototype
  
  3. manufacture: Mazda
     model: MX-5
     convertible: softtop
  </code></pre>
  
  <p>Now as you can see all cars are tagged with their manufacture and model, but the other categories don't all match.  Note that a car can only have one of each category.  IE. A car can only have one manufacturer.</p>
  
  <p>I want to design a database to support a search for all Mercedes, or to be able to list all manufactures.</p>
  
  <p>My current design is something like this:</p>
  
  <pre><code>vehicles
    int vid
    String vin
  
  vehicleTags
    int vid
    int tid
  
  tags
    int tid
    String tag
    int cid
  
  categories
    int cid
    String category
  </code></pre>
  
  <p>I have all the right primary and foreign keys in place, except I can't handle the case where each car can only have one manufacturer.  Or can I?</p>
  
  <p>Can I add a foreign key constraint to the composite primary key in vehicleTags?  IE. Could I add a constraint such that the composite primary key (vid, tid) can only be added to vehicleTags only if there isn't already a row in vehicleTags such that for the same vid, there isn't already a tid in the with the same cid?</p>
  
  <p>My guess is no.  I think the solution to this problem is add a cid column to vehicleTags, and make the new composite primary key (vid, cid).  It would look like:</p>
  
  <pre><code>vehicleTags
    int vid
    int cid
    int tid
  </code></pre>
  
  <p>This would prevent a car from having two manufacturers, but now I have duplicated the information that tid is in cid.</p>
  
  <p>What should my schema be?</p>
  
  <p>Tom noticed this problem in my database schema in my previous question, <a href="https://stackoverflow.com/questions/372399/how-do-you-do-many-to-many-table-outer-joins">How do you do many to many table outer joins?</a></p>
  
  <p><strong>EDIT</strong><br/>
  I know that in the example manufacture should really be a column in the vehicle table, but let's say you can't do that.  The example is just an example.</p>