> This can get tricky if you have 100s of thousands or millions of _roles_
If you use something like a bitmap index (e.g. Roaring Bitmaps), you can easily manage roles in-line on each user row if the role membership is typically sparse.
You can still maintain a separate Roles table as a canonical reference, but you would no longer need to join on it to determine who has what.
Yes and no: there is no bitmap SQL type, but at least MSSQL packs multiple BIT-columns on the same table into the same word/byte and then uses bit tests for filtering. I suspect Oracle and Postgresql can do the same, but I don't know for sure.
If you use something like a bitmap index (e.g. Roaring Bitmaps), you can easily manage roles in-line on each user row if the role membership is typically sparse.
You can still maintain a separate Roles table as a canonical reference, but you would no longer need to join on it to determine who has what.