Drizzle Team•12mo ago

SQL noob trying to make schema Drizzle ORM

My confusion comes from how to structure the database so that I don't save duplicate data and when I update I don't leave the data (like promoting agent to lead) I have watched a lot of tutorials but I just can't seem to get the mental model on how to do this or even what would be the best way to do this. where when a user logs in he is assigned a role

enum UserRole {
  SUPER = "super",
  ADMIN = "admin",
  HOD = "hod",
  MANAGER = "manager",
  LEAD = "lead",
  AGENT = "agent",
  USER = "user",
}

enum UserRole {
  SUPER = "super",
  ADMIN = "admin",
  HOD = "hod",
  MANAGER = "manager",
  LEAD = "lead",
  AGENT = "agent",
  USER = "user",
}

now those roles define what actions the user can perform. - agent has one lead - lead has one manager - lead is in charge of one brand - manager has one hod - manager is in charge of multiple brands The code is here https://stackoverflow.com/questions/77560683/sql-noob-trying-to-make-schema-drizzle-orm

Stack Overflow

SQL noob trying to make schema Drizzle ORM

My confusion comes from how to structure the database so that I don't save duplicate data and when I update I don't leave the data (like promoting agent to lead) I am designing my db structure for ...

8 Replies

Revan•12mo ago

@arafays So the problem here is your foreign keys are the wrong way around. In SQL, if a manager controls many brands, then the relationship needs to go on the brand table, right now you have it on the manager table as brandId, but if that were the case a manager could only control 1 brand, so instead put managerId on the brand table, and then to get the brands for a manager you can query every brand that has the managerId of <id> (hope this makes sense) When using drizzle you need to declare relationships twice, once for the database (like .references(() => managers.id)) and once to tell drizzle (with relations objects). From your stack overflow post,I can see you are getting the error: Error: There is not enough information to infer relation "__public__.managers.brands" This usually means that a relationship only has 1 half of the required objects, to declare managers+brands you need to have managersRelations AND brandsRelations:

export const managersRelations = relations(managers, ({many}) => {
  brands: many(brands)
})

export const brandsRelations = relations(brands, ({one) => {
  manager: one(managers, { fields: [brands.managerId], references: [managers.id] })
})

export const managersRelations = relations(managers, ({many}) => {
  brands: many(brands)
})

export const brandsRelations = relations(brands, ({one) => {
  manager: one(managers, { fields: [brands.managerId], references: [managers.id] })
})

arafaysOP•12mo ago

@Revan Thank you raven I got it working. I guess I will learn if the relationships are correct while I make the application

tzezar•12mo ago

you should name columns in snake_case and stick to one convention while naming tables (plural/singular)

tzezar•12mo ago

https://www.sqlstyle.guide/#naming-conventions

SQL style guide by Simon Holywell

A consistent code style guide for SQL to ensure legible and maintainable projects

tzezar•12mo ago

this is fine if you want to do redundant job; planning database is one of the most important things

arafaysOP•12mo ago

you mean @skelaw

// change
createdByUserId: varchar("createdByUserId", { length: 255 }).notNull(),
// to (also table name)
createdByUserId: varchar("created_by_user_id", { length: 255 }).notNull(),

// change
createdByUserId: varchar("createdByUserId", { length: 255 }).notNull(),
// to (also table name)
createdByUserId: varchar("created_by_user_id", { length: 255 }).notNull(),

arafaysOP•12mo ago

@skelaw and yeah I am being extra causious making these tables thats why I have spent almost three days thinking about what and this would work serarched db diagram software and made this to get clarity https://dbdiagram.io/d/Agent-Calculation-6557f95d3be14957873bb285

A Free Database Designer for Developers and Analysts

Quick and simple free tool to help you draw your database relationship diagrams and flow quickly using just keyboard

tzezar•12mo ago

yea, overall the most important thing is to be consistent in naming, but 95% time I see snake_case. Your schema looks fine to me

Gaming

Programming

SQL noob trying to make schema Drizzle ORM