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;