5 ms·
> 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 mana
by bob1029 5y ago
> 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 5y agoOut of curiosity, is there any kind of support for bitmaps in mainstream databases?
- bob1029 5y agoYou 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 5y agoYes 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.