PostgresSQL : store in one field forein keys (several)

Question

I want to store data for my project in which a company has, beside its own definition, access to one or many features of an external application. I want to represent the link between those respective tables and store the id of the features that one company has access to into the company table.

I would like some help building my MCD and choosing the type of field for my feature column. Here is the structure :

• Primary table: Features
  Columns:

  • id: number, primary key
  • name: string
  • decription: string
  • price: number

• Foreign table: Company
  Columns:

  • id
  • name: string
  • description: string
  • features: foreign key to Features

The foreign key is the link obviously, but I'm lost, because each company can have one or more feature. I'm confused about the most efficient way to store the features available to the company, and thus which data type I must choose to store this information.

I know this is a many-to-many association (well, I guess), where one company can have access to multiple features and one features can be accessible for many companies.

Can I use an array of foreign keys? Do I need to make an association table? (In this case, I don't know to model it.)

Answer 1

Never keep foreign keys in an array, JSON or any other composite data type. For one, this will make joins complicated and slow, and then you have no way to enforce foreign key constraints.

Create an association table (also known as "junction table"): it need only consist of two columns, which are foreign keys to the respective tables. The primary key should consist of these two columns; there is no need to create an artificial primary key:

CREATE TABLE company_features (
   company_id bigint REFERENCES company NOT NULL,
   feature_id bigint REFERENCES features NOT NULL,
   PRIMARY KEY (company_id, feature_id)
);