3

It might be the case that my initial design is flawed, but let's start from there and see whether there is a good enough approach.

I have two entities, say, A and B that are very similar but not identical so the sets of columns are different. For those reasons, there are two distinct tables, A and B.

That said, I would like to create another table that should describe both A and B with a set of fields that are applicable to both A and B. Let's call this table entity_settings.

Its schema is not intuitive to figure out:

  1. My first idea is to use some kind of entity_type flag and entity_id from either A or B depending on the type with a UNIQUE constraint on (entity_type, entity_id). Such a definition makes it impossible to have a normal foreign constraint because this entity_id consists of ids from two tables.

  2. Another option is having A_id and B_id that can be null but reference the ID fields from the corresponding tables. This makes sense and provides referential integrity, but inserts into this table are going to be pretty clumsy.

  3. The only option I am also considering is having two separate tables for A and B so that each table has its own child. This looks a bit wasteful in terms of the number of tables, but is probably the cleanest option in terms of design.

As almost always, I have a feeling that there is something I am missing and would highly appreciate it if someone could suggest any other options.

EDIT:

In the second option, it also makes sense to add a CHECK constraint like CHECK((A_id IS NOT NULL AND B_id IS NULL)) OR (A_id IS NULL AND B IS NOT NULL))

1
  • 2
    This looks like your typical inheritance (type-subtype) model, but you seem to have lost the supertype for A and B. Once you find it, it'll become trivial to see what entity_settings should reference.
    – mustaccio
    Commented Oct 3, 2022 at 18:56

2 Answers 2

4

You are not missing anything, this is a known tough problem in relational databases. The concept doesn't lend itself to models like that naturally.

Your solution 2. is a good one, with the check constraint.

Solution 3. is also possible. Perhaps you can use partitioning, so that you can have a single partitioned table entity_settings, and the partitions have foreign keys to A and B. If that doesn't cause problems with your queries or other parts of the system, it's a decent solution.

3
  • Thank you very much for your answer. As for solution 3, do I understand correctly, you are suggesting creating 2 partitions and adding different constraints (referring to A and B respectively)? But what would be the partitioning criterion?
    – Don Draper
    Commented Oct 3, 2022 at 19:06
  • 1
    Yes, you got that. The partitioning key could be entity_type. But as I said, it might not be feasible. Commented Oct 3, 2022 at 19:14
  • Thank you! Option 2 seems to be a bit more preferrable now that I have your advice, but at least Option 3 is something I can think about and learn for a future use case :)
    – Don Draper
    Commented Oct 3, 2022 at 19:16
1

An approach could be to use inherited table and partition table.

Declare parent table with shared fields, but never use to add record.

CREATE TABLE IF NOT EXISTS public.prod_gen
(
    division_id smallint NOT NULL,
    ctx text COLLATE pg_catalog."default" NOT NULL,
    code text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT prod_gen_pk PRIMARY KEY (division_id, ctx, code)
)

TABLESPACE pg_default;

Create specific tables as childs of parent table and insert data.

CREATE TABLE IF NOT EXISTS public.prod_cl
(
    -- Inherited from table public.prod_gen: division_id smallint NOT NULL,
    -- Inherited from table public.prod_gen: ctx text COLLATE pg_catalog."default" NOT NULL,
    -- Inherited from table public.prod_gen: code text COLLATE pg_catalog."default" NOT NULL,
    cl_attr text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT prod_cl_pk PRIMARY KEY (division_id, code),
    CONSTRAINT prod_cl_chk_context CHECK (ctx = 'cl'::text) NOT VALID
)
    INHERITS (public.prod_gen)
TABLESPACE pg_default;

CREATE TABLE IF NOT EXISTS public.prod_sec
(
    -- Inherited from table public.prod_gen: division_id smallint NOT NULL,
    -- Inherited from table public.prod_gen: ctx text COLLATE pg_catalog."default" NOT NULL,
    -- Inherited from table public.prod_gen: code text COLLATE pg_catalog."default" NOT NULL,
    sec_attr text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT prod_sec_pk PRIMARY KEY (division_id, code),
    CONSTRAINT prod_sec_chk_context CHECK (ctx = 'sec'::text) NOT VALID
)
    INHERITS (public.prod_gen)
TABLESPACE pg_default;

It's needed a field share leading (conceptually) in wich table records must be stored. In my case the field ctx.

Finally create partitioned table

CREATE TABLE IF NOT EXISTS public.prod_elem
(
    division_id smallint NOT NULL,
    ctx text COLLATE pg_catalog."default" NOT NULL,
    code text COLLATE pg_catalog."default" NOT NULL,
    el_id smallint NOT NULL,
    el_value text COLLATE pg_catalog."default",
    CONSTRAINT prod_elem_pkey PRIMARY KEY (division_id, ctx, code, el_id),
    CONSTRAINT prod_elem_chk_context CHECK (ctx = ANY (ARRAY['cl'::text, 'sec'::text])) NOT VALID
) PARTITION BY LIST (ctx);


CREATE TABLE public.prod_elem_cl PARTITION OF public.prod_elem
( 
    CONSTRAINT prod_elem_cl_fk FOREIGN KEY (division_id, code)
        REFERENCES public.prod_cl (division_id, code) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT
        NOT VALID
)
    FOR VALUES IN ('cl')
TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.prod_elem_cl
    OWNER to postgres;
-- Index: fki_p

-- DROP INDEX IF EXISTS public.fki_p;

CREATE INDEX fki_p
    ON public.prod_elem_cl USING btree
    (division_id ASC NULLS LAST, code COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;
CREATE TABLE public.prod_elem_sec PARTITION OF public.prod_elem
( 
    CONSTRAINT prod_elem_sec_fk FOREIGN KEY (division_id, code)
        REFERENCES public.prod_sec (division_id, code) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
)
    FOR VALUES IN ('sec')
TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.prod_elem_sec
    OWNER to postgres;
-- Index: fki_prod_elem_cl_fk

-- DROP INDEX IF EXISTS public.fki_prod_elem_cl_fk;

CREATE INDEX fki_prod_elem_cl_fk
    ON public.prod_elem_sec USING btree
    (division_id ASC NULLS LAST, code COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

And now we can insert records:

insert into prod_cl values(1,'cl','a001','foo');
insert into prod_sec values(1,'sec','b001','bar');
insert into prod_elem values(1,'cl','a001',5,'somevalue')
,(1,'sec','b001',8,'some other value')
;

I aware this solution doesn't fit any needs, but foreign key related to parent table (prod_gen) can refers only to record stored in parent table.

This is a know limit.

However it could be a reasonable approach when detail tables are the same structure and semantic and change just main table (in my case tables for ctx=cl and ctx=sec)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.