Re: Inheritance question

msteele(at)inet-interactif(dot)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(dot)Elphick(at)lfix(dot)co(dot)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