Fog Creek Software
Discussion Board




Table inheritance: worth using?

I'm tempted to use table inheritance feature that PostgreSQL provides, but feel somewhat wary of it because it doesn't seem to be widely used. Are there any caveats in it, besides no support from any CASE tool I know of?

Egor
Monday, August 23, 2004

What does table inheritance 'do'?

I am Jack's curiosity
Monday, August 23, 2004

I believe the issue is the value of inheritance in tables.  What do you get for introducing the complexity.  What can you get with inheritance that is unavailable in views/sub-views? 

The low number of people embracing it is because we cannot find a good answer to this question.

Anonanonanon
Monday, August 23, 2004

Jack,

Postgres supports inheritance like this:

CREATE TABLE COPMPUTER (
  ip_number varchar(20),
  location  varchar2(30),
  processor_speed integer,
);

CREATE TABLE SERVER (
  rack_location varchar2(30)
) inherits (COMPUTER)

CREATE TABLE WORKSTATION(
  screen_size integer
) INHERITS(COMPUTER)

This way the SERVER and WORKSTATION tables can hold
specific stuff, that only applies to servers or workstations,
and both inherits the rest of the properties from the table
named COMPUTER.

Google for PostgreSQL documentation to read more on table inheritance.

Patrik
Monday, August 23, 2004

A more careful reading of PostgreSQL manual revealed that table inheritance totally breaks foreign and primary keys, which "will probably be fixed in some future release". Case closed.

BTW, SQL99 seems to include table inheritance (UNDER keyword). Anyone knows a DBMS besides Informix that supports it?

Egor
Monday, August 23, 2004

I think the 'proper' relational way of doing is something like this (in stupid pseudo-sql as I can't be arsed looking up the actual syntax)

create table computer (
  computerid integer not null primary key,
  name varchar,
  location varchar,
  ...etc...
);

create table computer_server (
  computerid integer not null foreign key (computer) on delete cascade
  server-specific fields here
);

create table computer_workstation (
  computerid integer foreign key (computer) on delete cascade
  workstation-specific fields here
);

Just have extra relations that add possible extra info to the main relation. Can't think of anything you can do with inheritance that you couldn't do with this sort of schema and a (left) join or two. The cascade on delete ensures any extra workstation/server info gets deleted if the relevant computer info gets deleted, and you keep referential integrity. You can then create views based on this schema if you want things that 'look' like inherited tables with both the computer and workstation fields, for example.

Matt
Monday, August 23, 2004

DB2 has been doing this for years via their DDS syntax. (This is from the legacy AS/400 point of view. I believe the keyword was REFFLD. I don't remember the SQL equivalent though.) Inheritance can be useful in elliminating the amount of coding required to define a database. When using the join tables approach you have to setup both the base and the extension table plus define a combining view for the two. Some databases might not let you update a view which could mean additional coding for database writes. Not knowing much of how Postgre-SQL implements this feature I can't say much in its favor. However, I definitely see the value of simplified SQL-DDL without the need to concern yourself with the details of the underlying schema. I believe that suggesting join tables in lieu of this feature is an unravelling of a good idea for the sake of not understanding/appreciating the benefits that it offers. That said, I'll still go on record saying that it has the flaw of locking you to a specific DBMS vendor if the syntax is non-standard. Personally, I'd just go with a good O/R mapper rather than futz with the details. I don't know what MS developers use but in Java land there's plenty, like Hibernate, iBatis, and OBJ.

Clifton Craig
Monday, August 23, 2004

How is referential integrity enforced with INHERITANCE(cascade or restrict)?

How do we declare/implement relationships to derived tables when the FK is in the base table? How is the RI declared and enforced for this case?

Is there a instanceof keyword/function for querying?
SELECT * from Base where instanceof A or instanceof B

(equivalent to (select ... from A) union (select ... from B))

Related to above, can we declare/implement abstract base tables? If yes, how do we query on the abstract base class?

Is there an easy way to denormalize the inheritance model?

Say,

Table Base
table A inherits Base
table A ->> B on Base.Fk1
table A ->> C on A.Fk2

How do we denormalize A ->> C? Normally we would join A and C together; however, in this case the A inherits Base relationship does not hold anymore.

Inheritance is a kind of relationship in the relational model; it can be implemented in 3 different ways, depending what you are trying to do. From this perspective inheritance is ambiguous.

To conclude and answer the initial question: no.

Dino
Monday, August 23, 2004

Matt,

The "proper" relational way is with entity sub/super types and automatic "view" creation. PostgreSQL's method is sort of a way to use bastard-child SQL syntax to accomplish this.

Although yours is nonetheless effective it's the "SQL way", not the "real" way.

Captain McFly
Monday, August 23, 2004

"How do we declare/implement relationships to derived tables when the FK is in the base table? How is the RI declared and enforced for this case?"

Views.

Captain McFly
Monday, August 23, 2004

Thanks for the info. I thought it was probably not much different in concept than OOP inheritance.

Perhaps this is useful in situations like the one Aussie Chick had awhile back? An old general DB question...

Your company sells 4 types of widgets. Each of the types has at least one completely separate and unique property. All of the widgets do share some common traits, such as price and weight. Is it better to make a separate table for each widget type or to put all the data into one table where you know you will have NULLS in rows where the property doesn't exist for that item?

Maybe it is better to use inheritance... if you are using Postgres.

I am not Jack anymore because I can't keep coming up with suitable facets of Jack to be
Monday, August 23, 2004

The problem is that a percentage of the current PostgreSQL developers view table inheritance as a sorta vestigal apendix-like structure.  So it's been on the "we should fix this one day" list for a while with nobody actually touching it.

The problem is, there's a few discrete different tasks that can be accomplished with inheritance the way it is, and nobody's quite sure what the right way to make it work should be.  Because you can use it to create a hierarchial table structure where you have basic attributes and child tables with inherited attributes.  In this case, you want to inherit every bit of semantics and indexing from the parent table.  However, you also may want to have a set of abstract tables that a application defined table should use.  You want some semantics and you want each child to have it's own index.  Furthermore, in this "mixin" case, you want multiple inheritance.  And this becomes painful because none of the constraints and such were designed with the possibility of inheritance.

So, the problem is, people are using it the way it currently is and would be annoyed if their code was now broken.  It's not something that anybody's going to pay for the development of.  So they've left it at the state that it was when PostgreSQL was pushing the limits as a research project and follow-on project to Ingres.  And there's not the incredible applications that some of the *other* object-oriented stuff (like defining types and indicies for a column) have to offer.

Which is unfortunate, because, if shaped properly, it could be a most awesome tool.

Flamebait Sr.
Monday, August 23, 2004

> Is it better to make a separate table for each widget
> type or to put all the data into one table where you
> know you will have NULLS in rows where the property
> doesn't exist for that item?

I tend to go the route of storing the common info in individual fields in a single 'Widgets' table, along with a 'WidgetType' column.  The specifics then get stuffed into XML and stored in a 'Details' column.  Queries on common info are easy, while querying into the 'Details' column is more difficult (unless your DBMS has good XML support).

.
Monday, August 23, 2004

When I said 'proper relational' I meant that in the purer relational algebra sense. Which doesn't have anything like 'entity subtypes', unless I'm sorely mistaken.

Matt
Monday, August 23, 2004

> Is it better to make a separate table for each widget
> type or to put all the data into one table where you
> know you will have NULLS in rows where the property
> doesn't exist for that item?

Better? 
You have several options if you go this way:
1.  Put everything in its own table
- Purists love this and can make a good argument that if the widgets have different attributes they are not the same.
2. Put all common features in a base table and all the unique in specific tables.
- Sort of the 1+ version.  You keep common things together and only deal with exceptions.  The problem with this one is like a consensus, no one really gets what they want and everyone has to do extra work to get what they do.
3. Put everything in one table.
- Not pure, but certainly fast and easier to access if conceptually they share the majority of attributes.  In addition, the old days are gone: Space is cheap and most tables support nulls with near zero space usage.  A tried and true method.

There are probably more, however in the end, the issue is how much is really in common?  Are we talking about types of fruit or types of vehicles or stuff in a plant?

Anonanonanon
Monday, August 23, 2004

Keep these models separate in your mind:

Conceptual Model
e.g. E-R or ORM

Logical/Representational Model
e.g. Relational Hierarchical or Network Model

Physical/Implementation Model
e.g. FireBird, PostgresSQL,

Inheritance is an E-R concept with the 3 possible relational mappings pointed out.

hth
k

Karel
Tuesday, August 24, 2004

What a wonderful OOP world.


Tuesday, August 24, 2004

Ah, I think I mean something different by 'relational'. I am thinking about the relational algebra. Nevermind.

Matt
Tuesday, August 24, 2004

The relational model is expressed in domains, relational schemas, and constraints (schema invariants).

Schemas are also called intensions and are what you know as table descriptions.

tuples that conform to the intension are called the extension and are what you know as rows.

The relational algebra have operators that manipulate extensions to give more extensions. like project and join.
Relational algebra is imperative/procedural like your regular prog language - do this then that etc

SQL is based on a relational calculus. Relational Calculus is declarative (think prolog) and operate on Intensions. Higher level - say what you want not how to achieve.

On comp.database.theory they slaughter you if you ask your question 'wrong'

Karel
Tuesday, August 24, 2004

*  Recent Topics

*  Fog Creek Home