Inheritance is a concept from object-oriented databases. It opens up interesting new possibilities of database design.
Let's create two tables: A table
cities and a table
capitals. Naturally, capitals are also cities,
so you want some way to show the capitals implicitly when you
list all cities. If you're really clever you might invent some
scheme like this:
CREATE TABLE capitals ( name text, population real, altitude int, -- (in ft) state char(2) ); CREATE TABLE non_capitals ( name text, population real, altitude int -- (in ft) ); CREATE VIEW cities AS SELECT name, population, altitude FROM capitals UNION SELECT name, population, altitude FROM non_capitals;
This works OK as far as querying goes, but it gets ugly when you need to update several rows, to name one thing.
A better solution is this:
CREATE TABLE cities ( name text, population real, altitude int -- (in ft) ); CREATE TABLE capitals ( state char(2) ) INHERITS (cities);
In this case, a row of
inherits all columns (name, population,
and altitude) from its parent,
The type of the column name is
text, a native PostgreSQL type for variable length
character strings. State capitals have an extra column, state,
that shows their state. In PostgreSQL, a table can inherit from zero or
more other tables.
For example, the following query finds the names of all cities, including state capitals, that are located at an altitude over 500 ft.:
SELECT name, altitude FROM cities WHERE altitude > 500;
name | altitude -----------+---------- Las Vegas | 2174 Mariposa | 1953 Madison | 845 (3 rows)
On the other hand, the following query finds all the cities that are not state capitals and are situated at an altitude of 500 ft. or higher:
SELECT name, altitude FROM ONLY cities WHERE altitude > 500;
name | altitude -----------+---------- Las Vegas | 2174 Mariposa | 1953 (2 rows)
Here the ONLY before cities indicates that the query should be run over
cities table, and not
cities in the
inheritance hierarchy. Many of the commands that we have already
discussed -- SELECT, UPDATE and DELETE -- support
this ONLY notation.
dealing with multiple addresses: my favorite solution is to store all the unique addresses in one table and use an xref table to join the "parties" and all their types of addresses. If you include from and to date columns in the xref, you can keep track of change history -- useful for reporting :)
Of course, this means that changing an address gets a bit more complex: you'll need to check who else uses that same address and whether the change is "right" for all parties.