title: Items and specialized items: Multiple tables with duplicate columns, main table and detail table, or ...?
body: <p>I need to store data related to "items" where there will be various different item types, all with common attributes, and then each type with its own additional attributes. I expect this is a common requirement; what's the best-practices solution? We're using SQL Server.</p>
  
  <p>Let's use a made-up example:</p>
  
  <p><strong>Vehicle</strong> has</p>
  
  <ul>
  <li>Price</li>
  <li>Make</li>
  <li>Model</li>
  <li>Owner</li>
  </ul>
  
  <p>(In our real data, there will be 10-15 common columns.)</p>
  
  <p><strong>Car</strong> is a Vehicle plus:</p>
  
  <ul>
  <li>Style (sedan, sports, etc.)</li>
  <li>Color</li>
  <li>EngineSize</li>
  </ul>
  
  <p><strong>Boat</strong> is a Vehicle plus:</p>
  
  <ul>
  <li>Displacement</li>
  <li>PortOfOrigin</li>
  </ul>
  
  <p>...etc. for several types of things. In our real data, each specialized type will typically add 2-5 columns; there will be 5 types to start with. We'll be adding types over time, but probably only 3 or 4 more in total (if that). Adding types requires development, so it's not like "tags" which can be added willy-nilly by end users. We assume adding a type will require changes to the DB and client tiers, and probably the mid-tier as well. That's totally fine.</p>
  
  <p>We will do lots of queries across all of the items (vehicles, in the example above); we only worry about the details of a specific item type (Car, Boat) rarely.</p>
  
  <p>I see four ways to store this data:</p>
  
  <ol>
  <li>Separate tables for Cars, Boats, etc., with duplicated columns.</li>
  <li>One table with the <code>Vehicle</code> data, a table for the additional <code>Car</code> data, and a table for the additional <code>Boat</code> data.</li>
  <li>One table of items, a separate table of item attributes with a row per additional attribute. E.g., a soft schema for the details.</li>
  <li>One table with generic columns given meaning only by non-DB code.</li>
  </ol>
  
  <p>Looking at each:</p>
  
  <ol>
  <li><p>Separate tables for Cars, Boats, etc., with duplicated columns. E.g., roughly:</p>
  
  <pre><code>CREATE TABLE [Cars] (
      [Id] IDENTITY PRIMARY KEY,
      [Price] DECIMAL (19, 4),
      [Make] NVARCHAR(200),
      [Model] NVARCHAR(200),
      [Owner] INT,
      [Id] INT PRIMARY KEY,
      [Style] NVARCHAR(200),
      [Color] NVARCHAR(200),
      [EngineSize] DECIMAL(19, 2)
  )
  CREATE TABLE [Boats] (
      [Id] IDENTITY PRIMARY KEY,
      [Price] DECIMAL (19, 4),
      [Make] NVARCHAR(200),
      [Model] NVARCHAR(200),
      [Owner] INT,
      [Id] INT PRIMARY KEY,
      [Displacement] DECIMAL(19, 4),
      [PortOfOrigin] NVARCHAR(200)
  )
  </code></pre>
  
  <p>Simple enough, Cars go in <code>Cars</code> and Boats go in <code>Boats</code>. If we add more vehicle types, we add a table. If we add another common column, we have to go back and add it to all the vehicle tables. Reporting against vehicles in general can be done against a union view of all of the tables (being careful about the <code>Id</code> column).</p></li>
  <li><p>One table with the <code>Vehicle</code> data, a table for the additional <code>Car</code> data, and a table for the additional <code>Boat</code> data. E.g., roughly:</p>
  
  <pre><code>CREATE TABLE [Vehicles] (
      [Id] IDENTITY PRIMARY KEY,
      [Price] DECIMAL (19, 4),
      [Make] NVARCHAR(200),
      [Model] NVARCHAR(200),
      [Owner] INT,
      [Type] INT        -- A type ID, e.g. "Car" vs. "Boat"
  )
  CREATE TABLE [Cars] (
      [Id] INT PRIMARY KEY,
      [Style] NVARCHAR(200),
      [Color] NVARCHAR(200),
      [EngineSize] DECIMAL(19, 2)
  )
  CREATE TABLE [Boats] (
      [Id] INT PRIMARY KEY,
      [Displacement] DECIMAL(19, 4),
      [PortOfOrigin] NVARCHAR(200)
  )
  </code></pre>
  
  <p>So every Car would have one row in <code>Vehicles</code> and one linked row in <code>Cars</code>. Every Boat would have one row in <code>Vehicles</code> and one linked row in <code>Boats</code>. If we add more vehicle types, we add a table. Reporting against vehicles in general can be done against just the <code>Vehicle</code> table. When retrieve details of a specific <code>Car</code> or <code>Boat</code>, we use a join.</p></li>
  <li><p>One table of items, a separate table of item attributes with a row per additional attribute. E.g., a soft schema for the details. E.g., roughly:</p>
  
  <pre><code>CREATE TABLE [Vehicles] (
      [Id] IDENTITY PRIMARY KEY,
      [Price] DECIMAL (19, 4),
      [Make] NVARCHAR(200),
      [Model] NVARCHAR(200),
      [Owner] INT,
      [Type] INT
  )
  CREATE TABLE [VehicleDetails] (
      [VehicleId] INT,
      [Name] NVARCHAR(200),
      [Value] NVARCHAR(MAX)
  )
  </code></pre>
  
  <p>So every Car gets one row in <code>Vehicles</code> and three rows in <code>VehicleDetails</code> (one each for "Style", "Color", and "EngineSize"). Reporting is largely done against the <code>Vehicle</code> table. Reporting on details starts getting messy fast. Soft schemas have their place, mostly around user-defined data, but I'm assuming this wouldn't be a good choice here.</p></li>
  <li><p>One table with generic columns given meaning only by non-DB code:</p>
  
  <pre><code>CREATE TABLE [Vehicles] (
      [Id] IDENTITY PRIMARY KEY,
      [Price] DECIMAL (19, 4),
      [Make] NVARCHAR(200),
      [Model] NVARCHAR(200),
      [Owner] INT,
      [Type] INT,
      [Detail01] NVARCHAR(MAX),
      [Detail02] NVARCHAR(MAX),
      [Detail03] NVARCHAR(MAX),
      [Detail04] NVARCHAR(MAX),
      [Detail05] NVARCHAR(MAX),
      [Detail06] NVARCHAR(MAX),
      [Detail07] NVARCHAR(MAX),
      [Detail08] NVARCHAR(MAX),
      [Detail09] NVARCHAR(MAX),
      [Detail10] NVARCHAR(MAX)
  )
  </code></pre>
  
  <p>So Car data would assign Style to <code>Detail01</code>, Color to <code>Detail02</code>, and EngineSize to <code>Detail03</code>; for Boats, we'd put Displacement in <code>Detail01</code> and PortOfOrigin in <code>Detail02</code>. Similarly, there may be a place for this with end-user defined schemas, but I'm guessing this wouldn't be a good answer when you can control the DB structure.</p></li>
  </ol>