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”