inheritance and primary and foreign keys
I'm new to RDBMS so what follows is probably old crusty questions but
might have some relation to the new foreign key implementation:
In database table inheritance, which may be different than inheritance as it is
in programming languages, shouldn't derived classes only store the additional
attribributes?
Shouldn't the inherited attributes be obtained by reference(s) to a row(s) in
the base class(es)?
Shouldn't when a derived class inherits a base class(es), that it
optionally inherit a user selected PRIMARY KEY of one of its base classes as its
PRIMARY KEY. The other PRIMARY KEYs of the base classes (rejected candidates),
are inherited as is like the rest of the other inherited attributes and
with their constraints intact?
I could imagine a syntax like:
CREATE TABLE child (... unique attributes of a child) INHERITS(parent1,
parent2) WITH PRIMARY KEY;
The WITH PRIMARY KEY, means that child takes the PRIMARY KEY of the first
base class listed in INHERITS() as its own PRIMARY KEY. If WITH PRIMARY
KEY is omitted, then the child is free to make one of its own unique attributes
its PRIMARY KEY.
This is totally different than how things are working and I'm probably wrong!
Under this scheme, an INSERT INTO child, results in an INSERT INTO each of its
base classes using those attributes it passed on to the child. A row in the
child class would really only contain storage of the unique attributes it adds
to the bases AND record OIDs for each base class that each point to a row
containing its attributes for the child instance. The record OIDS would be in
the order as they were in the INHERITS clause if that would matter.
What I describe is more like a TREE since the derived and base classes remain
tightly linked. In inheritance like in programming languages, or in real life,
attributes are just copied and the new entity has no linkage to its parents
anymore - it can be killed independent of its parent. I've noticed that in
Postgres, this is not the case. You have to DROP the derived classes before
you can DROP the base classes. Since there is already the some linkage, this
is not really inheritance in Postgres. A derived class here is like adding
limbs to a tree that gets it more specialized rather than totally different and
disconnected. To cut the tree down, you trim its limbs off first a little or
just plunge it all down.
A fully implemented tree-like inheritance allows the derived classes to act
as connected extensions of the base and optionally share/use an existing PRIMARY
KEY. I like the word EXTENDS instead of INHERITS for this. There is a
difference. EXTENDS is like what I am talking about, a tree structure that
builds up and remains connected. INHERITS to me means spawning off a new trunk
that initially has at least all of the properties of an existing tree - no
linkage back to the parent except to say it is of that class and some more.
The class resulting from being EXTENDS from from a parent remains connected to
the parent branch and shares its attributes. A class that INHERITS a base
class should be independent of the base. The base should be DROPpable
after the derived class has been CREATE?
--
Robert B. Easter
reaster@comptechnews.com
On Tue, 09 May 2000, Robert B. Easter wrote:
I'm new to RDBMS so what follows is probably old crusty questions but
might have some relation to the new foreign key implementation:
[snip]
I didn't mean to send this email. It was just a draft that I was playing with!
There are probaby very good reasons why INHERITS works the way it does.
Sorry.
Anyway, I like the idea of a separate treatement of INHERITS and EXTENDS for
implementing object hierarchy. For INHERIT, a child table just gets a
COPY of the structure of the parent with maybe some possibility to take
inherited attribute(s) and/or new attributes as a primary key. The parent can
be dropped since the child is independent of its parent. The parent would
'know' about its children, but the child need not know about the parent.
SELECT * from parent*; would work.
In EXTENDS, parent and child are connected branches that share attributes and
storage so that you can't drop the parent unless you drop dependent children
first. The children would again, have an ability to use inherited attribs as a
primary key, possibily in combination with new attributes. The default
might be that if the parent has a primary key, it will also be the
primary key of the child. The child and parent would be using the SAME key
with indexes etc shared. Inserting into the child results in insertion into the
parent branch of the inherited attribs and an insert into the child of just its
unique attribs. The child just extends its parent with more attributes.
SELECT * from parent*; would act just like it does in INHERITS.
Again, INHERITS is like starting a new independent trunk that initially gets the
structure of what it inherits. EXTENDS adds a branch to a connected tree
structure to create more specialization.
Anyhow, this kind of topic probably bores people and has been beaten into the
ground in the past. If there is an archive about topics like this in
PostgreSQL hackers, I'd like to know the time periods and I'd go read it.
I am able to get the system to act almost the way I want by doing this:
CREATE TABLE employee (
em_id SERIAL PRIMARY KEY,
name TEXT,
...
);
CREATE TABLE manager (
...
) INHERITS(employee);
The SERIAL type gets inherited with its modifier to use the same sequence as
employee does. Only thing missing is that em_id is not the PRIMARY KEY in
manager and I don't see how to do it.
--
Robert B. Easter
reaster@comptechnews.com