4 ms·
Separate tables or just one table to hold access control info between entities?
Say, I have three entity types `Senior`, `Junior_A` and `Junior_B`. Each entity specifies access control for other entities. So I can either create tables as follows:
Senior, Junior_A, Junior_B, AccessControl
where `AccessControl` holds access control details between all the entities. This simplifies the code but could lead to performance issues as everything is put into one table.
But, then I can also create the tables as follows:
Senior, Junior_A, Junior_B, AC_Senior_Senior, AC_Senior_Junior_A, AC_Senior_Junior_B, AC_Junior_A_Junior_A, AC_Junior_A_Junior_B, AC_Junior_B_Junior_B
where `AC_ABC_XYZ` is a table that holds access control information between entities `ABC` and `XYZ`. This complicates the code as the code now has to handle different tables depending on the entity types involved, but will improve performance.
Which design would you prefer, and why?
- PaulHoule 4y agoMost databases will construct indexes that will make queries on AccessControl efficient. In terms of performance I'd be more worried about concurrent update: a simple database might lock the whole table when you update it, others will try to lock a limited range. Breaking the table up into smaller tables might help, but possibly less than you think because the distribution of objects and accesses to objects will be lopsided in many cases. You might have 20 kinds of object but 1/3 of the accesses might go to one of them -- that limits the scalability benefits of adding more tables. The single table is easier codewise, the multiple tables could be a nightmare unless you use "Dynamic SQL" https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-dynamic-sql/ https://www.sqlservertutorial.net/sql-server-stored-procedur... which is unpopular in some circles for a few reasons, one of which is that somebody might be able to fool your code into writing queries that break security.
- mattbrewsbytes 4y agoI think single table with a column for the entity type/kind is TheWay™, adding indexes to columns that are commonly used for look-ups. This is an enterprise application pattern called Single Table Inheritance: https://en.wikipedia.org/wiki/Single_Table_Inheritance https://en.wikipedia.org/wiki/Single_Table_Inheritance or from the source: https://www.martinfowler.com/eaaCatalog/singleTableInheritance.html https://www.martinfowler.com/eaaCatalog/singleTableInheritan... There are trade-offs with all design decisions. In this case having columns that are un-used for some Models is a trade-off (and might be annoying when looking at a db table). If AccessControl implies a permissions type of system, depending on how many permissions there are, they could all be boolean columns in this table. Or if there are sets of permissions and users could have combinations of them (aka 1:many), you could have a Users table, AccessControl table (maybe call it Roles?) with roles/permissions, then a UserRoles table that joins a User to many AccessControl/Roles.