Re: One parent record with 3 possible child records

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Nic Mitchell <[email protected]>
To: JORGE MALDONADO <[email protected]>
Cc: [email protected]
Subject: Re: One parent record with 3 possible child records
Date: Thu, 2 May 2024 23:47:46 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAAY=A79usb2Lbr9nUyzxyRNWLQNc-w3oTBbtUGyC2a4E-qaX3A@mail.gmail.com>
References: <CAAY=A79usb2Lbr9nUyzxyRNWLQNc-w3oTBbtUGyC2a4E-qaX3A@mail.gmail.com>

On 02/05/2024 00:08, JORGE MALDONADO wrote:
> Hi,
> 
> I have one table that can have relations to only 1 of 3 possible tables. 
> For example: tblMain*,* tblOne, tblTwo and tblThree.
> 
> I will always have 1 record in tblMain but each record in this table 
> will be related to one record in tblOne OR one record in tblTwo OR one 
> record in tblThree.
> 
> The relation between tblMain and tblOne is 1:1.
> The relation between tblMain and tblTwo is 1:1.
> The relation between tblMain and tblThree is 1:1.
> 
> Is it better to set tblMain as parent or child?
> 
> This can be seen as if tblOne, tblTwo and tblThree 
> extend tblMain depending on a specific criteria.
> 
> Let's say that tableMain has a string field called "type" with the 
> following possible values: "residential", "industrial" and "energy".
> 
> I will always insert a record in tblMain but:
> * If type is "residential" then a record in tblOne is created and it is 
> associated to tblMain,
> * If type is "industrial" a record in tblTwo is created and it is 
> associated to tblMain
> * If type is "energy" a record in tblThree is created and it is 
> associated to tblMain.
> 
> I am not sure how to design a case like this. I will very much 
> appreciate your feedback.
> 
> Best regards,
> Jorge Maldonado
> 

I worked with a similar looking, I think, relationship for a time. The 
idea was that, using trigger functions, rows in the _object table - your 
tblMain - would be inserted/deleted only ever as a result of 
inserting/deleting rows in the tables _objecttype[1-3] - your tblOne, 
tblTwo and tblThree.

I came up with this attempting to solve a problem where I needed to be 
able to model groups that could contain objects of different types, each 
of which had to be stored in their own table.

So, here's the code...

CREATE TABLE public._objecttype (
	_id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 10001 ),
	_name text NOT NULL,
	CONSTRAINT _objecttype_pkey PRIMARY KEY (_id),
	CONSTRAINT _objecttype__name_key UNIQUE (_name)
);

COMMENT ON TABLE public._objecttype IS E'values in _name column will be 
_objectype1,  _objectype2, and _objectype3';

CREATE TABLE public._object (
	_id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 20001 ),
	_objecttype_id integer NOT NULL,
	CONSTRAINT _object_pkey PRIMARY KEY (_id)
);

CREATE TABLE public._objecttype1 (
	_object_id integer NOT NULL,
	_name text NOT NULL,
	CONSTRAINT _objecttype1_pkey PRIMARY KEY (_object_id),
	CONSTRAINT _objecttype1__name_key UNIQUE (_name)
);

CREATE TABLE public._objecttype2 (
	_object_id integer NOT NULL,
	_name text NOT NULL,
	CONSTRAINT _objecttype2_pkey PRIMARY KEY (_object_id),
	CONSTRAINT _objecttype2__name_key UNIQUE (_name)
);

CREATE TABLE public._objecttype3 (
	_object_id integer NOT NULL,
	_name text NOT NULL,
	CONSTRAINT _objecttype3_pkey PRIMARY KEY (_object_id),
	CONSTRAINT _objecttype3__name_key UNIQUE (_name)
);

CREATE FUNCTION public._tf_biu_createobject ()
	RETURNS trigger
	LANGUAGE plpgsql
	AS $$
begin
if NEW._object_id is NULL then
	WITH object_id AS (
     INSERT INTO _object (_objecttype_id)
     VALUES (
		(
		SELECT _objecttype._id
		FROM _objecttype
		WHERE _objecttype._name
		LIKE TG_TABLE_NAME
		)
	)
     RETURNING _id)
     SELECT * FROM object_id INTO NEW._object_id;
end if;
return NEW;
end;
$$;

CREATE TRIGGER _t_biu_createobject
	BEFORE INSERT OR UPDATE
	ON public._objecttype1
	FOR EACH ROW
	EXECUTE PROCEDURE public._tf_biu_createobject();

CREATE TRIGGER _t_biu_createobject
	BEFORE INSERT OR UPDATE
	ON public._objecttype2
	FOR EACH ROW
	EXECUTE PROCEDURE public._tf_biu_createobject();

CREATE TRIGGER _t_biu_createobject
	BEFORE INSERT OR UPDATE
	ON public._objecttype3
	FOR EACH ROW
	EXECUTE PROCEDURE public._tf_biu_createobject();

CREATE FUNCTION public._tf_ad_deleteobject ()
	RETURNS trigger
	LANGUAGE plpgsql
	AS $$
begin
   DELETE FROM _object
   WHERE _object._id = OLD._object_id;
   return NULL;
end
$$;

CREATE TRIGGER _t_ad_deleteobject
	AFTER DELETE
	ON public._objecttype1
	FOR EACH ROW
	EXECUTE PROCEDURE public._tf_ad_deleteobject();

CREATE TRIGGER _t_ad_deleteobject
	AFTER DELETE
	ON public._objecttype2
	FOR EACH ROW
	EXECUTE PROCEDURE public._tf_ad_deleteobject();

CREATE TRIGGER _t_ad_deleteobject
	AFTER DELETE
	ON public._objecttype3
	FOR EACH ROW
	EXECUTE PROCEDURE public._tf_ad_deleteobject();

ALTER TABLE public._object ADD CONSTRAINT _object__objecttype_id_fkey 
FOREIGN KEY (_objecttype_id)
REFERENCES public._objecttype (_id) MATCH SIMPLE
ON DELETE RESTRICT ON UPDATE NO ACTION;

ALTER TABLE public._objecttype1 ADD CONSTRAINT 
_objecttype1__object_id_fkey FOREIGN KEY (_object_id)
REFERENCES public._object (_id) MATCH SIMPLE
ON DELETE RESTRICT ON UPDATE NO ACTION;

ALTER TABLE public._objecttype2 ADD CONSTRAINT 
_objecttype2__object_id_fkey FOREIGN KEY (_object_id)
REFERENCES public._object (_id) MATCH SIMPLE
ON DELETE RESTRICT ON UPDATE NO ACTION;

ALTER TABLE public._objecttype3 ADD CONSTRAINT 
_objecttype3__object_id_fkey FOREIGN KEY (_object_id)
REFERENCES public._object (_id) MATCH SIMPLE
ON DELETE RESTRICT ON UPDATE NO ACTION;

view thread (9+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: One parent record with 3 possible child records
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox