title: PostgreSQL table inheritance and constraints
body: <p>I have a DDL for some tables similar to the following:</p>
  
  <pre><code>CREATE TABLE devices
  (
    device_uuid            uuid NOT NULL,
    manufacturer_uuid       NOT NULL,
    ...
    CONSTRAINT device_manufacturer_uuid_fkey FOREIGN_KEY (manufacturer_uuid)
        REFERENCES manufacturer (manufacturer_uuid) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT devices_device_uuid_key UNIQUE (device_uuid),
    CONSTRAINT devices_pkey PRIMARY KEY (device_uuid)
  );
  </code></pre>
  
  <p>I might have different types of <code>devices</code>, e.g. "peripherals", "power", "graphics", etc.</p>
  
  <p>There are two approaches I can take:</p>
  
  <ol>
  <li><p>Add a simple <code>type</code> column to this table</p>
  
  <ul>
  <li><p>Pros: I have a pretty simple database structure.</p></li>
  <li><p>Cons: There could potentially be hundreds of thousands of entries, which could lead to performance problems. The list of "devices" would be searched fairly regularly, and searching through all those entries to find all those of type "peripherals" every time might not be great.</p></li>
  </ul></li>
  <li><p>Inherit from the above table for every type.</p></li>
  </ol>
  
  <p>The DDL for this would look similar to the following (I believe):</p>
  
  <pre><code>CREATE TABLE devicesperipherals 
  (
    type                       device NOT NULL,
    CONSTRAINT devicesperipherals_manufacturer_uuid_fkey FOREIGN_KEY (manufacturer_uuid)
        REFERENCES manufacturer (manufacturer_uuid) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT devicesperipherals_device_uuid_key UNIQUE (device_uuid)
    CONSTRAINT devices_manufacturer_uuid_fkey FOREIGN_KEY (manufacturer_uuid)
        REFERENCES manufacturer (manufacturer_uuid) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION, 
  )
  INHERITS (devices)
  WITH (
    OIDS=FALSE
  );
  </code></pre>
  
  <ul>
  <li><p>Pros: From what I know about table inheritance, this approach should have better performance. </p></li>
  <li><p>Cons: More complex database structure, more space required for indexing.</p></li>
  </ul>
  
  <p>Which is the preferred approach in such a case? Why?</p>