Hey, I started setting up my database and I decided I needed a table for users -

users

. In there, I have

id, email, password, createdAt

and so on. I soon realized that my users need roles as well, so, I created the

roles

column where I was to add an array of roles (I would have a

roles

table, connecting it all), but realized that MySQL doesn't(?) have support for arrays like that. I then settled on simply creating another table -

userMeta

, where I simply store

id, role

(where

id

is the user's id), in here, multiple rows can have the same user id, but not the same role, so, if I had an user with an id

1

and I had 2 roles -

administrator, user

, my

usersMeta

table would look as follows:

id  role
---------
1   administrator
1   user

In order for me to determine what roles an user has, I'd just simply run

SELECT role from user_meta WHERE id=1

. While this works, I just don't feel this is the right way to do things. I don't want to store roles as JSON, because then I can't query everything freely based on roles an user has or whatever else. Here are my current models:

enum Roles {
  ADMIN
  MODERATOR
  USER
}

model User {
  id       Int    @id @default(autoincrement())
  username String @unique
  email    String @unique

  firstName String @map("first_name")
  lastName  String @map("last_name")

  createdAt DateTime @default(now()) @map("created_at")
}

model UserMeta {
  id   Int
  role Roles @default(USER)

  @@id([id, role])
}

model Role {
  id           Int          @id
}

The database works, it's how I want it to be represented, I just don't know how to write it the "Prisma way". For example, right now, the

id

in

usersMeta

is any string. I'd like there to be a relation where that id can ONLY be an id from the

User

model.

You need to add a foreign key for referential integrity. That way if you add a user id on the userMeta table that doesn’t exist on the users table, mySQL would complain.

Thank you for the quick response, going through it right now.

Also if you make a composite/compound key on the userMeta table of id and role, you’ll have what you want to avoid role duplication per user.

Here’s how you could define relation between User and UserMeta models to make sure that the

id

in UserMeta points to a valid user id in User model.

model User {
  id       Int    @id @default(autoincrement())
  username String @unique
  email    String @unique

  firstName String @map("first_name")
  lastName  String @map("last_name")

  createdAt DateTime   @default(now()) @map("created_at")
  UserMeta  UserMeta[]
}

model UserMeta {
  id   Int
  User User @relation(fields: [id], references: [id])
  role Roles @default(USER)

  @@id([id, role])
}