StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I don't think this is an easy problem to set up, but it is made harder by the very denormalized structure of the schema. I'm assuming that there is actually a hierarchical setup where each company has multiple divisions and each division has multiple sections. Then you should have three tables, <code>Companies</code>, <code>Divisions</code>, <code>Sections</code>, and the <code>Sections</code> table would have only an FK for its division. (Its company can be determined from that.)</p> <p>However, <code>Companies</code>, <code>Divisions</code>, and <code>Sections</code> share (at least) two attributes in common. They have parents (except for the highest level), and they can appear in <code>ResourcePermissions</code>. So what we have here conceptually calls out for <em>inheritance</em>. Since support for inheritance in pretty weak in most RDBMS (some in Postgresql, I don't know about SQL Server), you'll have to do much of the setup yourself with triggers. At the inheritance root is a table [pseudo DDL]</p> <pre><code>CorporateElement element_id SERIAL (PK, AutoIncrement, etc.) parent int (FK references CorporateElement.id) level int or enum (Division, Section, etc., but not as text, as an enum or an FK into a list of these) ResourcePermissions resource_id int (FK references Resource.resource_id) element_id int (FK references CorporateElement.element_id) </code></pre> <p>Your <code>Section</code> and so-on tables inherit their key from <code>element_id</code> but their text names and other data are local to their own tables. Next you need the <code>WITH RECURSIVE</code> capabilities of SQL Server. I'm going to leave the answer incomplete here (will edit later), because there are a few ways of going about that—whether to join before or after the <code>RECURSIVE</code> part—and I need to think them through.</p> <p><strong>[edit]</strong> OK, here's a sample query, but based on refactoring the schema. I haven't tested it, but it should give a list of all resources with permissions. Minor mods could add those with none at any level. And the structure is flexible with respect to adding more levels of corporate entity.</p> <pre><code>WITH RECURSIVE permissions_search(resource_id, element_id, parent, permission) AS ( SELECT resource_id, element_id, parent, permission FROM resources JOIN resource_permissions ON resources.resource_id= resource_permissions.resource_id JOIN corporate_elements ON corporate_elements.elements_id=resource_permissions.elements_id WHERE corporate_element.level=section /* enum or magic int value */ UNION ALL SELECT resource_id, element_id, permission FROM permissions_search ps JOIN resource_permissions ON resources.resource_id= resource_permissions.resource_id JOIN corporate_elements ON corporate_elements.elements_id=resource_permissions.elements_id WHERE (corporate_elements.elements_id=ps.parent) AND (ps.permission IS NULL) ) SELECT * FROM permissions_search WHERE permission IS NOT NULL; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload