how about inheritance in postgres?
CREATE TABLE "tblBase"
(
id serial NOT NULL, -- serial type is my assumption.
"SomeData" integer,
CONSTRAINT "tblBase_pkey" PRIMARY KEY (id )
)
WITH (
OIDS=FALSE
);
CREATE TABLE "tblDerived1"
(
-- Inherited from table "tblBase": id integer NOT NULL DEFAULT nextval('"tblBase_id_seq"'::regclass),
-- Inherited from table "tblBase": "SomeData" integer,
"Data1" integer,
CONSTRAINT "tblDerived1_pkey" PRIMARY KEY (id )
)
INHERITS ("tblBase")
WITH (
OIDS=FALSE
);
CREATE TABLE "tblDerived2"
(
-- Inherited from table "tblBase": id integer NOT NULL DEFAULT nextval('"tblBase_id_seq"'::regclass),
-- Inherited from table "tblBase": "SomeData" integer,
"Data2" text,
CONSTRAINT "tblDerived2_pkey" PRIMARY KEY (id )
)
INHERITS ("tblBase")
WITH (
OIDS=FALSE
);
inheritance is described in doc here: http://www.postgresql.org/docs/9.1/static/ddl-inherit.html
With this approach all IDs will use the same sequence so there will not be duplicated PKs in inherited tables.
This could be also modeled with "standard" SQL approach without redundant information. Solution depends on requirements.
Regards,
Bartek
2012/4/17 Nils Gösche <cartan@xxxxxxxxx>
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
