CODEWITHSUNDEEP
sqlpostgresql
kevingilbert100
kevingilbert100

Reputation: 1333

Array of IDs in column referencing different table's row

Say I have a table containing different categories.. then another table where a column must reference array of multiple categories within the first.

What is this called and what is the best way to do this?

Postgres Dialect :)

Category Id | Category Name
1           | Category 1
2           | Category 2

Product Id | Product Name | Categories
1          | Product 1    | 1,2

Upvotes: 2

Views: 1614

Answers (1)

Nick
Nick

Reputation: 2513

It is called just integer array, int[] (or int8[] if you need 8-byte integers).

create table category (
  id serial primary key,
  name text not null
);

create table product (
  id bigserial primary key,
  name test not null,
  category_ids int[] not null
);

You can use this approach and even add GIN index over product.category_ids to speed up lookups like select * from product where '{2}'::int[] <@ category_ids; -- but the obvious drawback here is that Postgres will not be able to ensure referential integrity -- you cannot define a foreign key for int[].

Alternatively, you could use jsonb data type instead of int[] and also add GIN index on that column to speed up searches.

If you want to have FKs (which is very good and right desire), just follow the traditional approach, EAV:

create table category (
  id serial primary key,
  name text not null
);

create table product (
  id bigserial primary key,
  name test not null
);

create table product2category (
  product_id int8 not null references product(id),
  category_id int not null references category(id),
  primary key (product_id, category_id)
);

There is a number of articles discovering pros and cons of EAV vs. int[] (aka "intarray") or jsonb approaches, here is one of them: http://coussej.github.io/2016/01/14/Replacing-EAV-with-JSONB-in-PostgreSQL/ -- I recommend to google that topic and learn, it will help to decide what is better for your case.

Upvotes: 6

Related Questions