Postgres Arrays and PHP -Part 1

The other day I found myself designing a database schema for a collection of musicians each of which plays one or more instruments. The result looked something like this.

CREATE TABLE musicians
(
    id bigserial NOT NULL,
    name text NOT NULL,
    PRIMARY KEY (id)
);

CREATE TYPE musical_instrument AS ENUM
    ('guitar', 'piano', 'bass', 'trumpet', 'drums');

CREATE TABLE musician_instruments
(
    musician_id bigint NOT NULL,
    instrument musical_instrument NOT NULL,
    "position" integer NOT NULL,
    PRIMARY KEY (musician_id, instrument)
);

As you can see the table musician_instruments is used to associate musical instruments to a musician. It has an additional column called position because the order of instruments is relevant for the application.

When I squinted real hard I saw that I had in front of me a simple array disguised table. So I thought, why not use the array type that Postgres provides and see where it leads me. A research project if you might.

ALTER TABLE public.musicians
    ADD COLUMN instruments musical_instrument[] NOT NULL;
Continue reading “Postgres Arrays and PHP -Part 1”