Classes (Object Oriented) in PostgreSQL question

Started by Nikola Milutinovicover 24 years ago2 messagesgeneral
Jump to latest
#1Nikola Milutinovic
Nikola.Milutinovic@ev.co.yu

Hi all.

I'm in the process of redesigning my database. I have seen some space
for object design in my data schema. Namely, I'm modeling my company and
it has organizational units: "parts", "sectors", "divisions" and
"subdivisions". Each of these look alike and form a tree.

So I thought to create a table "org_unit" containing common fields and
just inferit them for a particular unit type. Something like:

CREATE TABLE org_unit (
id int4
PRIMARY KEY,
name text,
manager_id int4,
ordering int2,
print boolean
);

CREATE TABLE ou_part (
) INFERITS (org_unit);

CREATE TABLE ou_sector (
part_id int4,
FOREIGN KEY( part_id ) REFERENCES ou_part
) INHERITS (org_unit);
...
CREATE TABLE official_mail (
id int4 PRIMARY KEY,
e_mail text,
org_unit_id int4,
FOREIGN KEY( org_unit_id ) REFERENCES org_unit
);

Will this work? Both the foreign key in ou_* and in official_mail?

Nix.

#2Stephan Szabo
sszabo@megazone23.bigpanda.com
In reply to: Nikola Milutinovic (#1)
Re: Classes (Object Oriented) in PostgreSQL question

Hi all.

I'm in the process of redesigning my database. I have seen some space
for object design in my data schema. Namely, I'm modeling my company and
it has organizational units: "parts", "sectors", "divisions" and
"subdivisions". Each of these look alike and form a tree.

So I thought to create a table "org_unit" containing common fields and
just inferit them for a particular unit type. Something like:

CREATE TABLE org_unit (
id int4
PRIMARY KEY,
name text,
manager_id int4,
ordering int2,
print boolean
);

CREATE TABLE ou_part (
) INFERITS (org_unit);

CREATE TABLE ou_sector (
part_id int4,
FOREIGN KEY( part_id ) REFERENCES ou_part
) INHERITS (org_unit);
...
CREATE TABLE official_mail (
id int4 PRIMARY KEY,
e_mail text,
org_unit_id int4,
FOREIGN KEY( org_unit_id ) REFERENCES org_unit
);

Will this work? Both the foreign key in ou_* and in official_mail?

Probably not how you want. The references will currently
only reference the exact table you specify, not any of the
children.