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;