Kysely•2y ago

Should database tables mimic form fields?

Hey everyone, this question is not Kysely specific just wanna point that out don't know where to ask but here. I have a form for business registration that looks like this

22 Replies

NazCodelandOP•2y ago

NazCodelandOP•2y ago

However, in my database, I don't have a table that minimcs this form 1:1, I have a user table that looks like this

model User {
  id           String        @id @unique @default(dbgenerated("gen_random_uuid()")) @db.Uuid /// @zod.custom.omit([model, input]) 
  createdAt    DateTime      @default(now()) @db.Date /// @zod.custom.omit([model, input]) 
  // trigger
  updatedAt    DateTime?     @db.Timestamp() /// @zod.custom.omit([model, input]) 
  // trigger
  lastLogIn    DateTime?     @db.Timestamp() /// @zod.custom.omit([model, input]) 
  email        String        @unique
  profile      Profile?
  business     Business?
  appointments Appointment[]
  role         Role

  // https://authjs.dev/reference/adapter/prisma
  // does this apply to all providers
  emailVerified DateTime?
  accounts      Account[]
  sessions      Session[]
}

model User {
  id           String        @id @unique @default(dbgenerated("gen_random_uuid()")) @db.Uuid /// @zod.custom.omit([model, input]) 
  createdAt    DateTime      @default(now()) @db.Date /// @zod.custom.omit([model, input]) 
  // trigger
  updatedAt    DateTime?     @db.Timestamp() /// @zod.custom.omit([model, input]) 
  // trigger
  lastLogIn    DateTime?     @db.Timestamp() /// @zod.custom.omit([model, input]) 
  email        String        @unique
  profile      Profile?
  business     Business?
  appointments Appointment[]
  role         Role

  // https://authjs.dev/reference/adapter/prisma
  // does this apply to all providers
  emailVerified DateTime?
  accounts      Account[]
  sessions      Session[]
}

and a business table

model Business {
  id               String             @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid /// @zod.custom.omit([model, input]) 
  name             String             @db.VarChar(100)
  email            String
  // TODO: person signing up phone could be diff. then business phone, just like email
  phone            String
  description      String?
  bannerUrl        String?
  address          Address?           @relation(fields: [addressId], references: [id])
  // since the addressId is @unique, each Business must have unique address
  addressId        String?            @unique @db.Uuid
  workingHours     WorkingHours?
  services         Service[]
  businessCategory BusinessCategory[]
  appointments     Appointment[]
  user             User               @relation(fields: [userId], references: [id])
  userId           String             @unique @db.Uuid /// @zod.custom.omit([model, input]) 
}

model Business {
  id               String             @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid /// @zod.custom.omit([model, input]) 
  name             String             @db.VarChar(100)
  email            String
  // TODO: person signing up phone could be diff. then business phone, just like email
  phone            String
  description      String?
  bannerUrl        String?
  address          Address?           @relation(fields: [addressId], references: [id])
  // since the addressId is @unique, each Business must have unique address
  addressId        String?            @unique @db.Uuid
  workingHours     WorkingHours?
  services         Service[]
  businessCategory BusinessCategory[]
  appointments     Appointment[]
  user             User               @relation(fields: [userId], references: [id])
  userId           String             @unique @db.Uuid /// @zod.custom.omit([model, input]) 
}

I am using the Models to create ZodSchemas so... I can't validate the formData with a ZodSchema because it doesn't contain all those fields I could manually create a zodSchema that has all those fields but that made me think about my question, does it make more sense to have a table that mimics my formData 1:1 + addtional fields or if that's not bad/good

Unknown User•2y ago

Message Not Public

NazCodelandOP•2y ago

The zod Schema is for validating form data but it's out of sync with the actual form fields

Unknown User•2y ago

Message Not Public

NazCodelandOP•2y ago

ya maybe I explained it bad, but it's not being input directly by user input my zodSchemas are being auto-generated by a package that generates them based off of my Prisma schema ^ that's what my prisma schema looks like which has a lot more fields than my form input so my zodSchema has a lot more inputs then my form input field so if I validate it with that zodSchema it fails

Unknown User•2y ago

Message Not Public

NazCodelandOP•2y ago

If you don't mind, lets use this model for an example

model Business {
  id               String             @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid /// @zod.custom.omit([model, input]) 
  name             String             @db.VarChar(100)
  email            String
  // TODO: person signing up phone could be diff. then business phone, just like email
  phone            String
  description      String?
  bannerUrl        String?
  address          Address?           @relation(fields: [addressId], references: [id])
  // since the addressId is @unique, each Business must have unique address
  addressId        String?            @unique @db.Uuid
  workingHours     WorkingHours?
  services         Service[]
  businessCategory BusinessCategory[]
  appointments     Appointment[]
  user             User               @relation(fields: [userId], references: [id])
  userId           String             @unique @db.Uuid /// @zod.custom.omit([model, input]) 
}

model Business {
  id               String             @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid /// @zod.custom.omit([model, input]) 
  name             String             @db.VarChar(100)
  email            String
  // TODO: person signing up phone could be diff. then business phone, just like email
  phone            String
  description      String?
  bannerUrl        String?
  address          Address?           @relation(fields: [addressId], references: [id])
  // since the addressId is @unique, each Business must have unique address
  addressId        String?            @unique @db.Uuid
  workingHours     WorkingHours?
  services         Service[]
  businessCategory BusinessCategory[]
  appointments     Appointment[]
  user             User               @relation(fields: [userId], references: [id])
  userId           String             @unique @db.Uuid /// @zod.custom.omit([model, input]) 
}

inside my database, I have this Table. so, the package that generates zodSchemas from my Prisma model outputs this based on the above Model

export const BusinessSchema = z.object({
  // omitted: id: z.string(),
  email: z.string(),
  name: z.string(),
  phone: z.string(),
  description: z.string().nullable(),
  bannerUrl: z.string().nullable(),
  addressId: z.string().nullable(),
  // omitted: userId: z.string(),
})

export const BusinessSchema = z.object({
  // omitted: id: z.string(),
  email: z.string(),
  name: z.string(),
  phone: z.string(),
  description: z.string().nullable(),
  bannerUrl: z.string().nullable(),
  addressId: z.string().nullable(),
  // omitted: userId: z.string(),
})

but all these fields are not on my form

Unknown User•2y ago

Message Not Public

NazCodelandOP•2y ago

right, that's what I'm trying to do what do you mean by resolver?

Unknown User•2y ago

Message Not Public

NazCodelandOP•2y ago

ok ya so I'm not sure if I should adjust my Models so that I have one that atleast mimics my form input fields and all the other properties can be option or, just manually make a zodSchema for the form input if I do that, that gets rid of the usefulness of the zodSchema package, I guess I could delete that

Unknown User•2y ago

Message Not Public

NazCodelandOP•2y ago

right, the id being auto generated by the db and it's omitted from the zodSchema in that case, it works what's making me question things is that

Unknown User•2y ago

Message Not Public

NazCodelandOP•2y ago

only input field that this form contains that my Business Model doesn't is the First & Last Name inside my business model, I have

...
user             User               @relation(fields: [userId], references: [id])
  userId           String             @unique @db.Uuid /// @zod.custom.omit([model, input])

...
user             User               @relation(fields: [userId], references: [id])
  userId           String             @unique @db.Uuid /// @zod.custom.omit([model, input])

Unknown User•2y ago

Message Not Public

NazCodelandOP•2y ago

I guess for that scenario, I would just make a emailSchema is that the right way to go about it?

Unknown User•2y ago

Message Not Public

NazCodelandOP•2y ago

ok, thank you for the help Tom!

Unknown User•2y ago

Message Not Public

NazCodelandOP•2y ago

😄 always

Gaming

Programming

Should database tables mimic form fields?