Re: Feature Proposal: Constant Values in Columns or Foreign Keys

Hi!

I have a little feature proposal. Let me try to explain the motivation
behind it.

Suppose our application has two types of objects, looking somewhat like
this:

abstract class Base
{
   public int Id;
   public int SomeData;
}

class Derived1 : Base
{
   public int Data1;
}

class Derived2 : Base
{
   public string Data2;
}

There are many ways of modeling this in a relational database. I am
interested in this one:

CREATE TYPE derived_type AS ENUM ('derived1', 'derived2);

CREATE TABLE base (
   id int PRIMARY KEY,
   some_data int NOT NULL,
   type derived_type NOT NULL
);

CREATE UNIQUE INDEX base_derived_type_index ON base (id, derived_type);

CREATE TABLE derived1 (
  id int PRIMARY KEY,
  data1 int NOT NULL,
  type derived_type NOT NULL CHECK (type = 'derived1'),
  FOREIGN KEY (id, type) REFERENCES base (id, type) ON DELETE CASCADE
)

CREATE TABLE derived2 (
   id int PRIMARY KEY,
   data2 text NOT NULL,
   type derived_type NOT NULL CHECK (type = 'derived2'),
   FOREIGN KEY (id, type) REFERENCES base (id, type) ON DELETE CASCADE
)

Note that the type column in derived1 and derived2 ensures that there is at
most one row in either derived1 or derived2 which refers to a given row in
"base".

This works fine, actually. What bugs me, though, is the redundant data in
the type columns of derived1 and derived2. It would be nice if I could
either declare the columns as constant (so the data wouldn't be stored on
disk anymore), or (even better) use a constant value in the foreign keys, as
in

   FOREIGN KEY (id, 'derived2') REFERENCES base (id, type) ON DELETE
CASCADE

In the latter case, I could omit the type column of derived1 and derived2
altogether.

I suspect that it wouldn't be terribly hard to implement this. What do you
think? Wouldn't this be nice to have?

Regards,
--
Nils Gösche
"Don't ask for whom the <CTRL-G> tolls."



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general