according to DB theory:
1NF: Table faithfully represents a relation and has no repeating groups
2NF: No non-prime attribute in the table is functionally dependent on a proper subset of anycandidate key.
so these constants are not in line with this approach.
You can implement one to one relation:
CREATE TABLE "tblBase"(
id text NOT NULL,
"SomeData" integer,
CONSTRAINT "tblBase_pkey" PRIMARY KEY (id )
);
CREATE TABLE "tblDerived1"(
id text NOT NULL,
"Data1" integer,
CONSTRAINT "tblDerived1_pkey" PRIMARY KEY (id ),
CONSTRAINT "tblDerived1_id_fkey" FOREIGN KEY (id)
REFERENCES "tblBase" (id) MATCH FULL
ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
);
CREATE TABLE "tblDerived2"(
id text NOT NULL,
"Data1" text,
CONSTRAINT "tblDerived2_pkey" PRIMARY KEY (id ),
CONSTRAINT "tblDerived2_id_fkey" FOREIGN KEY (id)
REFERENCES "tblBase" (id) MATCH FULL
ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
);
So, You don't have any duplicated PK, every class is described in separate entity (table).
If Your business logic needs to recognize classes in other way You can use views, with constant field which is not stored on disk:
CREATE VIEW "vDerived1"
AS
SELECT *, 'DERIVED1'::text as "ClassType" FROM
"tblBase" NATURAL JOIN "tblDerived1";
CREATE VIEW "vDerived2"
AS
SELECT *, 'DERIVED2'::text as "ClassType" FROM
"tblBase" NATURAL JOIN "tblDerived2";
The problem is that "tblDerived1".id is not guaranteed to be not present in
"tblDerived2". This could be handled e.g. by trigger (before update) on both tables (cross check), or using a kind of "middleware", I mean a function which is responsible do perform inserts.
In my opinion it should be possible to recognize proper class based on its attributes, so it should be quite easy to implement this function. Function could be overloaded (same name, different set of attributes).
Regards,
Bartek
Bartek
