Is there a way to implement an organization with many types without duplicating this organization?

Question

I am new at postgresql and database systems.

I have two tables like this:

Organization Table

CREATE TABLE public."Organization"
(
    org_id integer NOT NULL DEFAULT nextval('"Organization_org_id_seq"'::regclass),
    org_name "char" NOT NULL,
    org_description "char" NOT NULL,
    org_email citext COLLATE pg_catalog."default" NOT NULL,
    org_phone "char" NOT NULL,
    bt_id integer,
    CONSTRAINT "Organization_pkey" PRIMARY KEY (org_id),
    CONSTRAINT bt_id1 FOREIGN KEY (bt_id)
        REFERENCES public.business_type (businesstype_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID
)

Business Type table

CREATE TABLE public.business_type
(
    businesstype_id integer NOT NULL DEFAULT nextval('business_type_businesstype_id_seq'::regclass),
    businesstype_description "char" NOT NULL,
    CONSTRAINT business_type_pkey PRIMARY KEY (businesstype_id)
)

Business type has 4 values. I would like to implement a system in which an organization might have a type between 0 and 4, without the need to duplicate the organization to have a different type. With my schema, for an organization to have two types, I would need to insert the organization two times, having org_id different and bt_id different.

Is there a way to implement a schema where an organization has the same org_id, and multiple bt_id, without the need to insert it multiple times? Further more, I would like for the business type to always be different. For example, an organization might have business type 1, or 1,2,3, but not 1,1.

Answer 1

You are describing a many-to-many relationship between organizations and business types. To store that relation, you can create a third table:

create table organization_business_type (
    org_id integer 
        references organization(org_id)
        on delete cascade,
    businesstype_id integer 
        references business_type(businesstype_id)
        on delete cascade,
    primary key (org_id, businesstype_id)
);

This allows 0 to N business types per organization, while forbidding duplicates (the primary key does the latter).

Accordingly, you would need to remove column bt_id from the organization table.

Side note: In Postgres, don't use upper case table or column names: public."Organization" should really be public.organization.