Drizzle Team•3mo ago

User Settings architecture

Hey, I am very new to everything related with backend and I recently started my full stack side project. The application will have "modules" for the user to choose from, they are stored in the DB:

export const user = pgTable('user', {
  id: serial('id').primaryKey().notNull(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  firstName: varchar('first_name', { length: 255 }).notNull(),
  lastName: varchar('last_name', { length: 255 }).notNull(),
})

export const moduleType = pgEnum('type', [
  'sport',
  'finance',
  'family',
  'school',
])

export const module = pgTable('module', {
  id: serial('id').primaryKey().notNull(),
  type: moduleType('type').notNull(),
})

export const user = pgTable('user', {
  id: serial('id').primaryKey().notNull(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  firstName: varchar('first_name', { length: 255 }).notNull(),
  lastName: varchar('last_name', { length: 255 }).notNull(),
})

export const moduleType = pgEnum('type', [
  'sport',
  'finance',
  'family',
  'school',
])

export const module = pgTable('module', {
  id: serial('id').primaryKey().notNull(),
  type: moduleType('type').notNull(),
})

and I am wondering what is the best practice/pattern to make a relation between those two tables with a requirement that the user selects multiple modules NOTE that there will also be theme, currency etc. for the user to select Is creating a third table like preferences a good idea? if soo how should the table look like ?

1 Reply

Trustody•3mo ago

Just throw in a third table to link users and their chosen modules. Something like this:

export const preferences = pgTable('preferences', {
  userId: integer('user_id').references(() => user.id).notNull(),
  moduleId: integer('module_id').references(() => module.id).notNull(),
  createdAt: timestamp('created_at').defaultNow(),
}, (table) => ({
  primaryKey: ['userId', 'moduleId'],
}))

export const preferences = pgTable('preferences', {
  userId: integer('user_id').references(() => user.id).notNull(),
  moduleId: integer('module_id').references(() => module.id).notNull(),
  createdAt: timestamp('created_at').defaultNow(),
}, (table) => ({
  primaryKey: ['userId', 'moduleId'],
}))

This way, users can pick multiple modules without any hassle. The combo of userId and moduleId keeps them from selecting the same one twice, and it all stays nice and clean in the database.

Gaming

Programming

User Settings architecture