Foreign key for array column

Question

I have a table:

CREATE TABLE methods
(
    method_id serial PRIMARY KEY,
    method_name varchar(100)
);

I now want to create a table with the following columns:

CREATE TABLE experiments 
(
    method integer[] REFERENCES methods(method_id),
    trials integer
);

I get an error:

Key columns "method" and "method_id" are of incompatible types: integer[] and integer.

I understand that the columns have to be the same type and I also saw that some tried to tackle this foreign key on array issue already:

PostgreSQL 9.3 development: Array ELEMENT Foreign Keys

My interest is that 'method' should be an array of integers referencing 'method_id' from table 'methods' but I can't figure it out. I thought that the link above might be a solution, but seems that was not implemented (?)

Some posts propose using junction/join tables:

Foreign key constraint on array member?

I am an absolute beginner and I could not figure it out yet. For me, it is a matter of understanding first how to tackle this issue of a foreign key on an array. Multiple methods can be considered as forming a class of methods. Each class can have a certain number of trials.

Answer 1

As you simply cannot create a foreign key against an array column, you should store the experiments in the proper relational way. This is what you mention as a junction table.

It is a table like the following:

CREATE TABLE experiments (
    method_id integer REFERENCES methods (method_id),
    method_class_id integer REFERENCES method_class (method_class_id)
);

Here you will have one row per method-class pair (which, in turn, may mean several trials, based on your comments above), instead of just one per trial as in your current design. This means a slight storage overhead, but it gives you the flexibility - queries will use proper joins which can be supported by indexes, for example. Also, removing methods from a class will be much easier.

Note: I suppose above that you have a method_class table, too.