[v4] When create a "Has one" relation on collection, it creates a mapping table

Bug report

Describe the bug

A problem occured when creating a "Has one" relation between collections.
Assume there are "employee" and "company" collections. Then "employees" and "companies" tables exist in the database.
I created a "Has one" relation between "employee" and "company" collections on Strapi CMS.
In v3, "company_id" column is created in "employees" table. But, in v4, creates the mapping table "employees_company_id_links".
I'm not sure if this is an intended feature or a bug, so I'm creating this issue.

Steps to reproduce the behavior

1. Create "employee" and "company" collections.
2. Create a "employee has one Company" relation in the "employee" collection.
3. Check the database table.

Expected behavior

A "company_id" column is added as a foreign key to the "employees" table.

Screenshots

[Strapi v3]

[Strapi v4]

Code snippets

https://github.com/lejinseop/strapi-v4-database-relation-test

System

• Node.js version: 14.15.5
• NPM version: 8.1.4
• Strapi version: 4.0.0-beta.17
• Database: Postgres 13.4
• Operating system: MAC OS (PostgresDB in docker container)

Additional context

none

[alexandrebodin]

Hi, this is indeed inteded. All the relations are now links table.

Planning for the upcoming features (versionning / publciation workflow) etc we will rapidly be blocked by the simple foreign keys approach. To avoid future breaking changes we already changed the DB schema.

thanks for explanation!

[derrickmehaffy]

This issue has been mentioned on Strapi Community Forum. There might be relevant details there:

https://forum.strapi.io/t/strapi-4-creates-an-additional-table-for-a-one-to-one-relationship/13520/3

[robotex82]

TBH, no offense, but this feels weird.

Assume you have a years, a country and a settings table. Settings belong to a year and a country (lets assume i could create foreign keys in the settings table, like countryId and yearId). Now i want to have an index that ensures that i only can have one entry for a combination of year and country.

How am I supposed to implement such an index (or validation) if there are no simple foreign keys on one table?

[VadimGut]

TBH, no offense, but this feels weird.

Assume you have a years, a country and a settings table. Settings belong to a year and a country (lets assume i could create foreign keys in the settings table, like countryId and yearId). Now i want to have an index that ensures that i only can have one entry for a combination of year and country.

How am I supposed to implement such an index (or validation) if there are no simple foreign keys on one table?

Totally agreed
this is a major performance issue for a large data set
why not support both options for users who are not interested in versionning & publciation workflow
kind of opt out checkbox ?

[tonilopezmr]

@alexandrebodin Is there a way to turn off versioning & publications and not creating mapping tables?

I'm reading a lot of people asking, and I'd like to know an answer, even if the answer is "No", we would like to know why?

[derrickmehaffy]

@alexandrebodin Is there a way to turn off versioning & publications and not creating mapping tables?

I'm reading a lot of people asking, and I'd like to know an answer, even if the answer is "No", we would like to know why?

No largely because these won't be able to be turned off in v5, the features will be there even if they aren't being used and the mapping tables will be required in v5 regardless.