All Postgres relationships are many-to-many #4163
Replies: 2 comments 4 replies
-
Hey @valerioleo, Thanks for this well thought out enhancement idea. I've converted this to a discussion since we reserve issues for bugfixes. I think you're right about all of this. Coming from MongoDB we never had to worry about this and this was an area we compromised on when building out the relational model.
The way I've typically worked with this was to have a column for the relationship with a typical FK. We chose not to do this because it would require migrations and additional logic to build queries and join data for our relationship fields. It also was done to make fewer data migration scenarios when a config changes to use
I'm unfamiliar with this. Could you provide a little demostration of what it would look like using Drizzle orm? I would want to avoid breaking changes with this feature or it will have to coincide with a major release. I question how important this is to most Payload users to know if they would value this kind of change to justify the effort, both in our own development and any existing project migration requirements. Thanks, I am looking forward to more feedback here. |
Beta Was this translation helpful? Give feedback.
-
Thank you guys so much for the amazing works you've done with 3.0. Lots of big changes that are gonna take our product to a next level, thanks for the postgres migration 2.0 to 3.0 too, you guys thought about everything truly 🙇♂️ |
Beta Was this translation helpful? Give feedback.
-
Link to reproduction
No response
Describe the Bug
Hello Payload Team!
I've been working with Payload and came across some observations that I'd like to share, hoping they might lead to further improvements in our wonderful project.
For the sake of explaining the issue, imagine setting up entities
User
andUserSettings
. Where the user is related like this:After migrating and delving into the database schema through Prisma introspection, I've noticed some areas where we could enhance Payload's functionality, especially regarding 1-to-1 relationships.
Current Challenge:
While Payload's approach to handling entity relationships via separate tables is quite innovative and works well for many-to-many relationships, it poses certain challenges when dealing with 1-to-1 relationships, such as between
User
andSettings
.Key Points:
Handling 1-to-1 Relationships: Even when configuring a relationship in Payload with parameters like
hasMany: false
,unique: true
, andrequired: true
, the relationship is still managed as a many-to-many at the database level. This configuration doesn't translate into the expected database constraints, leading to potential data integrity issues.Application Layer vs. Database Layer Validations: My assumption is that validations like
unique
andrequired
for 1-to-1 relationships are enforced at the application layer rather than at the database layer. This approach can create challenges for those who wish to use other ORMs in tandem with Payload. External ORMs would not inherently recognize these constraints, potentially leading to inconsistencies and data integrity issues.Suggested Enhancements:
Enforcing Relationship Cardinality at DB Level: Implementing stricter enforcement of relationship cardinality at the database level would ensure that a
User
has exactly oneSettings
entity linked, maintaining data integrity and reflecting the intended database design.Refining the
path
Property: Transitioning thepath
property in relationship tables to an enumerated type would provide better structure and predictability, facilitating integration with external ORMs.Benefits of These Enhancements:
Improved ORM Compatibility: A more SQL-like approach to data storage would allow for better integration with various ORMs, enhancing Payload's flexibility in different development scenarios.
Data Integrity and Application Stability: By enforcing relationship constraints at the database level, we can ensure greater data integrity and reduce the risk of application errors due to unexpected data scenarios.
Simplified Data Operations: Standardizing the schema makes it easier for developers to use their chosen tools for database operations, enhancing overall development efficiency.
I am really excited about these potential improvements and believe they could make a significant positive impact on how Payload handles 1-to-1 relationships. Looking forward to hearing your thoughts and contributing to making these enhancements a reality!
To Reproduce
Create two collections and relate one to the other. For example create a User and UserSettings table and, in the User collection file specify
Payload Version
2
Adapters and Plugins
No response
Beta Was this translation helpful? Give feedback.
All reactions