Re: Inheritance question - Mailing list pgsql-general

msteele@inet-interactif.com wrote:
  >
  >Hi folks, I've got a question regarding inheritance.
  >
  >What are the advantages of using inheritance in the database
  >structure as opposed to using foreign keys? Doesn't
  >interitance mean that there will be much more duplication
  >of data in the database?
  >
  >If anyone has any examples of proper usage of inheritance,
  >please let met know. I've read the docs, and haven't
  >puzzled out the usefulness of inheritance.

There's an example below: part of a database schema.  The first table is
a dummy that is parent of all the others.  Notice that the fields 'id'
and 'name' are defined only in the top-level; they are automatically
included in every descendent.  The not null constraint on 'id' is
inherited, but unfortunately other constraints are not.  (I hope that
work will be done on inheritance for 7.2.)  Because of that deficiency,
the primary key is declared for each table separately, and there is no
convenient mechanism to ensure that keys are unique across the whole
hierarchy.

The benefit of inheritance is that all items of the same general class
can be treated as one table or else can be treated according to their
particular types.  If I say:

  SELECT * FROM resource;

I will get all records from resource and all its descendants.  (To get
rows from resource alone: SELECT * FROM ONLY resource;)  I will see only
the columns defined in resource, not the additional columns defined
in descendant tables.

Whether I want to look at resource or at a particular descendant is
dependant on what level of detail and specialisation I want.

Inheritance does not lead to duplication of rows; it does lead to
duplication of columns in several tables, but this is not a
problem.

Referential integrity (foreign keys) is a means of maintaining
internal consistency in a database.  Its job is to ensure that there
are, for example, no invoices with non-existent customer codes.  This
has nothing to do with inheritance.



====================== Extract from a schema ========================

-- Top level class for resources (bench, machine, outwork, tool)

CREATE TABLE resource
(
   id        VARCHAR(4),
   name      TEXT
,
PRIMARY KEY (id)

);


bray=# \d+ resource
                         Table "resource"
 Attribute |         Type         | Modifier |     Description
-----------+----------------------+----------+---------------------
 id        | character varying(4) | not null | Resource identifier
 name      | text                 |          | Resource name
Indices: resource_name,
         resource_pkey



CREATE TABLE pay_rates
(
   pay          NUMERIC(12,2)   NOT NULL
                                CHECK (pay >= 0),
   overhead     NUMERIC(12,2)   NOT NULL
                                CHECK (overhead >= 0),
   CONSTRAINT pay_relation CHECK (pay >= overhead)
,
PRIMARY KEY (id)

)
inherits (resource)
;

bray=# \d+ pay_rates
                           Table "pay_rates"
 Attribute |         Type         | Modifier |       Description
-----------+----------------------+----------+--------------------------
 id        | character varying(4) | not null | Resource identifier
 name      | text                 |          | Resource name
 pay       | numeric(12,2)        | not null | Worker's pay rate
 overhead  | numeric(12,2)        | not null | Additional overhead cost
Indices: pay_rates_name,
         pay_rates_pkey
Constraints: (pay >= overhead)
             (overhead >= '0'::"numeric")
             (pay >= '0'::"numeric")




-- Table describing factory benches

CREATE TABLE bench
(
   descr     TEXT,
   rate      NUMERIC(12,3)
,
PRIMARY KEY (id)

)
   INHERITS (resource)
;

CREATE UNIQUE INDEX bench_name ON bench (name);


bray=# \d+ bench
                            Table "bench"
 Attribute |         Type         | Modifier |      Description
-----------+----------------------+----------+------------------------
 id        | character varying(4) | not null | Resource identifier
 name      | text                 |          | Resource name
 descr     | text                 |          | Description
 rate      | numeric(12,3)        |          | Rate per hour in pence
Indices: bench_name,
         bench_pkey



-- Table describing machines used in production

CREATE TABLE machine
(
   descrip      TEXT            NOT NULL,
   maker        TEXT            NOT NULL,
   model        TEXT            NOT NULL,
   serialno     TEXT,
   inception    DATE            NOT NULL,
   supplier     VARCHAR(10)        NOT NULL
                                CONSTRAINT supplier
                                REFERENCES supplier (id)
                                        ON UPDATE CASCADE
                                        ON DELETE NO ACTION
                                        DEFERRABLE,
   life         INTEGER         NOT NULL
                                CHECK (life > 0 AND life < 20),
   lease_cost   NUMERIC(12,2)   NOT NULL,
   term_value   NUMERIC(12,2)   NOT NULL
                                DEFAULT 0,
   ann_usage    INTEGER         NOT NULL
                                DEFAULT 2000,
   fuel_cost    NUMERIC(12,2),
   servicing    NUMERIC(12,2),
   cool_heat    NUMERIC(12,2),
   attendance   NUMERIC(12,2),

   UNIQUE (maker, model, serialno)
,
PRIMARY KEY (id)

)
   INHERITS (resource)
;

CREATE UNIQUE INDEX machine_name ON machine (name);


bray=# \d+ machine
                                         Table "machine"
 Attribute  |         Type          |       Modifier        |             Description
------------+-----------------------+-----------------------+-------------------------------------
 id         | character varying(4)  | not null              | Resource identifier
 name       | text                  |                       | Resource name
 descrip    | text                  | not null              | Description
 maker      | text                  | not null              | Maker's name
 model      | text                  | not null              | Model name or number
 serialno   | text                  |                       | Serial number
 inception  | date                  | not null              | Date brought into service
 supplier   | character varying(10) | not null              | Supplier code
 life       | integer               | not null              | Life in years (at standard usage)
 lease_cost | numeric(12,2)         | not null              | Cost of leasing over machine's life
 term_value | numeric(12,2)         | not null default 0    | Expected value at end of life
 ann_usage  | integer               | not null default 2000 | Standard usage per year in hours
 fuel_cost  | numeric(12,2)         |                       | Cost of fuel per hour
 servicing  | numeric(12,2)         |                       | Cost of servicing, per year
 cool_heat  | numeric(12,2)         |                       | Cost of cooling or heating per year
 attendance | numeric(12,2)         |                       | Labour cost, per hour
Indices: machine_maker_key,
         machine_name,
         machine_pkey
Constraint: ((life > 0) AND (life < 20))



-- Table of tools, for example, moulds

CREATE TABLE tool
(
   maker       TEXT           NOT NULL,
   serial      TEXT,
   machine     VARCHAR(4)        NOT NULL
                              CONSTRAINT machine
                              REFERENCES machine (id)
                                        ON UPDATE CASCADE
                                        ON DELETE NO ACTION
                                        DEFERRABLE,
   inception   DATE           NOT NULL,
   supplier    VARCHAR(10)       NOT NULL
                              CONSTRAINT supplier
                              REFERENCES supplier (id)
                                        ON UPDATE CASCADE
                                        ON DELETE NO ACTION
                                        DEFERRABLE,
   life        INTEGER        NOT NULL,
   lease_cost  NUMERIC(12,2)  NOT NULL,
   term_value  NUMERIC(12,2)  NOT NULL
                              DEFAULT 0,
   ann_usage   INTEGER        NOT NULL
                              DEFAULT 2000,
   servicing   NUMERIC(12,2)
,
PRIMARY KEY (id)

)
INHERITS (resource)
;


CREATE UNIQUE INDEX tool_name ON tool (name);

bray=# \d+ tool
                                        Table "tool"
 Attribute  |         Type          |       Modifier        |          Description
------------+-----------------------+-----------------------+--------------------------------
 id         | character varying(4)  | not null              | Resource identifier
 name       | text                  |                       | Resource name
 maker      | text                  | not null              | Maker's name
 serial     | text                  |                       | Serial number
 machine    | character varying(4)  | not null              | Resource id of related machine
 inception  | date                  | not null              | Date brought into use
 supplier   | character varying(10) | not null              | Supplier code
 life       | integer               | not null              | Expected life in years
 lease_cost | numeric(12,2)         | not null              | Cost of leasing over life
 term_value | numeric(12,2)         | not null default 0    | Expected value at end of life
 ann_usage  | integer               | not null default 2000 | Standard annual usage in hours
 servicing  | numeric(12,2)         |                       | Annual service cost
Indices: tool_name,
         tool_pkey


--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "For by grace are ye saved through faith; and that not
      of yourselves; it is the gift of God, not of works,
      lest any man should boast."   Ephesians 2:8,9