public inbox for [email protected]help / color / mirror / Atom feed
One parent record with 3 possible child records 9+ messages / 7 participants [nested] [flat]
* One parent record with 3 possible child records @ 2024-05-01 23:08 JORGE MALDONADO <[email protected]> 0 siblings, 3 replies; 9+ messages in thread From: JORGE MALDONADO @ 2024-05-01 23:08 UTC (permalink / raw) To: pgsql-sql 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 ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: One parent record with 3 possible child records @ 2024-05-01 23:24 David G. Johnston <[email protected]> parent: JORGE MALDONADO <[email protected]> 2 siblings, 1 reply; 9+ messages in thread From: David G. Johnston @ 2024-05-01 23:24 UTC (permalink / raw) To: JORGE MALDONADO <[email protected]>; +Cc: pgsql-sql On Wednesday, May 1, 2024, JORGE MALDONADO <[email protected]> wrote: > > Let's say that tableMain has a string field called "type" with the > following possible values: "residential", "industrial" and "energy". > This, and have the same column in the other tables, but add a check constraint ensuring only the correct enum value can be specified. Then your foreign key from the child tables to main is both the ID and the type field. David J. ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: One parent record with 3 possible child records @ 2024-05-02 13:33 Sanjay Minni <[email protected]> parent: JORGE MALDONADO <[email protected]> 2 siblings, 1 reply; 9+ messages in thread From: Sanjay Minni @ 2024-05-02 13:33 UTC (permalink / raw) To: JORGE MALDONADO <[email protected]>; +Cc: pgsql-sql maybe unorthodox but if its certain to be 1:1 then why not flatten it to a single table with all the possible columns (tblMain+tblOne+tblTwo+tblThree). You can keep a flag to indicate the type. there may be a gain in simplicity without losing anything. Sanjay On Thu, May 2, 2024 at 4:39 AM JORGE MALDONADO <[email protected]> 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 > > ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: One parent record with 3 possible child records @ 2024-05-02 13:37 Rob Sargent <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Rob Sargent @ 2024-05-02 13:37 UTC (permalink / raw) To: David G. Johnston <[email protected]>; pgsql-sql; +Cc: JORGE MALDONADO <[email protected]> > On May 1, 2024, at 5:24 PM, David G. Johnston <[email protected]> wrote: > > On Wednesday, May 1, 2024, JORGE MALDONADO <[email protected] <mailto:[email protected]>> wrote: > > Let's say that tableMain has a string field called "type" with the following possible values: "residential", "industrial" and "energy". > > This, and have the same column in the other tables, but add a check constraint ensuring only the correct enum value can be specified. Then your foreign key from the child tables to main is both the ID and the type field. > > David J. Presuming the id is unique in parent and child, what’s the value of the type field in the foreign key? Are you saying the PK on tableMain is (type,id), perhaps for clustering purposes? ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: One parent record with 3 possible child records @ 2024-05-02 14:02 David G. Johnston <[email protected]> parent: Rob Sargent <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: David G. Johnston @ 2024-05-02 14:02 UTC (permalink / raw) To: Rob Sargent <[email protected]>; +Cc: pgsql-sql; JORGE MALDONADO <[email protected]> On Thu, May 2, 2024 at 6:37 AM Rob Sargent <[email protected]> wrote: > Presuming the id is unique in parent and child, what’s the value of the > type field in the foreign key? Are you saying the PK on tableMain is > (type,id), perhaps for clustering purposes? > > > Preventing the parent record from saying "tblOne" while the actual ID value is present on "tblTwo". David J. ^ permalink raw reply [nested|flat] 9+ messages in thread
* One parent record with 3 possible child records @ 2024-05-02 14:37 Wetmore, Matthew (CTR) <[email protected]> parent: Sanjay Minni <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Wetmore, Matthew (CTR) @ 2024-05-02 14:37 UTC (permalink / raw) To: Sanjay Minni <[email protected]>; JORGE MALDONADO <[email protected]>; +Cc: pgsql-sql Wouldn’t page size performance be decreased with a very wide table? Unless the columns are tight. If you are on a VM subsystem, that page bloat will be over the network from the memory controller. From: Sanjay Minni <[email protected]> Sent: Thursday, May 2, 2024 6:34 AM To: JORGE MALDONADO <[email protected]> Cc: [email protected] Subject: [EXTERNAL] Re: One parent record with 3 possible child records maybe unorthodox but if its certain to be 1:1 then why not flatten it to a single table with all the possible columns (tblMain+tblOne+tblTwo+tblThree). You can keep a flag to indicate the type. there may be a gain in simplicity without losing anything. Sanjay On Thu, May 2, 2024 at 4:39 AM JORGE MALDONADO <[email protected]<mailto:[email protected]>> 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 ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: One parent record with 3 possible child records @ 2024-05-02 15:12 Sanjay Minni <[email protected]> parent: Wetmore, Matthew (CTR) <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Sanjay Minni @ 2024-05-02 15:12 UTC (permalink / raw) To: Wetmore, Matthew (CTR) <[email protected]>; +Cc: JORGE MALDONADO <[email protected]>; pgsql-sql As I understand postgres does not reserve or use space for columns having no content. If that's so then the bloat will be insignificant . But off course the pros and cons of embedding the columns inline have to be weighed On Thu, 2 May, 2024, 8:07 pm Wetmore, Matthew (CTR), < [email protected]> wrote: > Wouldn’t page size performance be decreased with a very wide table? > Unless the columns are tight. If you are on a VM subsystem, that page > bloat will be over the network from the memory controller. > > > > *From:* Sanjay Minni <[email protected]> > *Sent:* Thursday, May 2, 2024 6:34 AM > *To:* JORGE MALDONADO <[email protected]> > *Cc:* [email protected] > *Subject:* [EXTERNAL] Re: One parent record with 3 possible child records > > > > maybe unorthodox but if its certain to be 1:1 then why not flatten it to a > single table with all the possible columns > (tblMain+tblOne+tblTwo+tblThree). You can keep a flag to indicate the type. > there may be a gain in simplicity without losing anything. > > > > Sanjay > > > > On Thu, May 2, 2024 at 4:39 AM JORGE MALDONADO <[email protected]> > 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 > > > > ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: One parent record with 3 possible child records @ 2024-05-02 18:28 Rob Sargent <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Rob Sargent @ 2024-05-02 18:28 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: pgsql-sql; JORGE MALDONADO <[email protected]> --Apple-Mail-314D3A98-AE09-452F-B04E-20E8205153A5 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable <html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D= utf-8"></head><body dir=3D"auto"><div dir=3D"ltr"></div><div dir=3D"ltr">Got= cha, thanks</div><div dir=3D"ltr"><br><blockquote type=3D"cite">On May 2, 20= 24, at 8:03=E2=80=AFAM, David G. Johnston <[email protected]>= wrote:<br><br></blockquote></div><blockquote type=3D"cite"><div dir=3D"ltr"= >=EF=BB=BF<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" sty= le=3D"font-family:arial,helvetica,sans-serif"><span style=3D"font-family:Ari= al,Helvetica,sans-serif">On Thu, May 2, 2024 at 6:37=E2=80=AFAM Rob Sargent &= lt;<a href=3D"mailto:[email protected]">[email protected]</a>> wr= ote:</span><br></div></div><div class=3D"gmail_quote"><blockquote class=3D"g= mail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,= 204,204);padding-left:1ex"><div style=3D"overflow-wrap: break-word;"><div><b= lockquote type=3D"cite">Presuming the id is unique in parent and child, what= =E2=80=99s the value of the type field in the foreign key? Are you say= ing the PK on tableMain is (type,id), perhaps for clustering purposes?<br></= blockquote></div><div><br></div></div></blockquote><div><br></div><div class= =3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">Preventi= ng the parent record from saying "tblOne" while the actual ID value is prese= nt on "tblTwo".</div><div class=3D"gmail_default" style=3D"font-family:arial= ,helvetica,sans-serif"><br></div><div class=3D"gmail_default" style=3D"font-= family:arial,helvetica,sans-serif">David J.</div><div class=3D"gmail_default= " style=3D"font-family:arial,helvetica,sans-serif"><br></div></div></div> </div></blockquote></body></html>= --Apple-Mail-314D3A98-AE09-452F-B04E-20E8205153A5-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: One parent record with 3 possible child records @ 2024-05-02 22:47 Nic Mitchell <[email protected]> parent: JORGE MALDONADO <[email protected]> 2 siblings, 0 replies; 9+ messages in thread From: Nic Mitchell @ 2024-05-02 22:47 UTC (permalink / raw) To: JORGE MALDONADO <[email protected]>; +Cc: pgsql-sql 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; ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-05-02 22:47 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-05-01 23:08 One parent record with 3 possible child records JORGE MALDONADO <[email protected]> 2024-05-01 23:24 ` David G. Johnston <[email protected]> 2024-05-02 13:37 ` Rob Sargent <[email protected]> 2024-05-02 14:02 ` David G. Johnston <[email protected]> 2024-05-02 18:28 ` Rob Sargent <[email protected]> 2024-05-02 13:33 ` Sanjay Minni <[email protected]> 2024-05-02 14:37 ` Wetmore, Matthew (CTR) <[email protected]> 2024-05-02 15:12 ` Sanjay Minni <[email protected]> 2024-05-02 22:47 ` Nic Mitchell <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox