[bob1029]

> 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.

[Yoric]

Out of curiosity, is there any kind of support for bitmaps in mainstream databases?

[bob1029]

You can just roll it yourself in the application logic and store as a raw binary column.

This all assuming you don't need to make queries along the axis of "give me every user with role X".

[tremon]

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.