Relations between tables unclear
Consider the following schema where a User can create a Post, and each post can have multiple versions of its content. What is the best way for a Post to store the
latestVersionIdlatestVersionId for quick access. I'm having trouble figuring that out, and also how to create a Post since a Post would need a PostVersion first, but a PostVersion needs a Post to link them together.export const posts = pgTable("posts", {
id: serial("id").primaryKey(),
authorId: integer("author_id").notNull(),
parentPostId: integer("parent_post_id"),
latestVersionId: integer("latest_version_id") // Is this the best way to store a reference to the latest post_version?
.notNull() // It doesn't make sense for the latestVersionId to be null, so I set it as notNull. But in this case, how can I create a Post or a PostVersion? Either of these need the other during their creation
.references(() => recordVersions.id),
});
export const postsRelations = relations(posts, ({ one, many }) => ({
author: one(users, {
fields: [records.authorId],
references: [users.id],
}),
parentPost: one(posts, {
fields: [posts.parentPostId],
references: [posts.id],
}),
versions: many(postVersions),
}));
export const postVersions = pgTable("post_versions", {
id: serial("id").primaryKey(),
authorId: integer("author_id").notNull(),
postId: integer("postId").notNull(),
content: jsonb("content"),
});
export const postVersionsRelations = relations(postVersions, ({ one }) => ({
author: one(users, {
fields: [postVersions.authorId],
references: [users.id],
}),
post: one(posts, {
fields: [postVersions.postId],
references: [posts.id],
}),
}));export const posts = pgTable("posts", {
id: serial("id").primaryKey(),
authorId: integer("author_id").notNull(),
parentPostId: integer("parent_post_id"),
latestVersionId: integer("latest_version_id") // Is this the best way to store a reference to the latest post_version?
.notNull() // It doesn't make sense for the latestVersionId to be null, so I set it as notNull. But in this case, how can I create a Post or a PostVersion? Either of these need the other during their creation
.references(() => recordVersions.id),
});
export const postsRelations = relations(posts, ({ one, many }) => ({
author: one(users, {
fields: [records.authorId],
references: [users.id],
}),
parentPost: one(posts, {
fields: [posts.parentPostId],
references: [posts.id],
}),
versions: many(postVersions),
}));
export const postVersions = pgTable("post_versions", {
id: serial("id").primaryKey(),
authorId: integer("author_id").notNull(),
postId: integer("postId").notNull(),
content: jsonb("content"),
});
export const postVersionsRelations = relations(postVersions, ({ one }) => ({
author: one(users, {
fields: [postVersions.authorId],
references: [users.id],
}),
post: one(posts, {
fields: [postVersions.postId],
references: [posts.id],
}),
}));