Depicting permissions to access table via E -R diagram

Question

I am designing a database schema for a web application. Its a first time I am designing a database with a commercial aim. I have started with drawing E-R diagrams after going through requirements phase.

My question is regarding conditions in schema.

I have a table category and sub category with one to many relationship between them. One category has many sub categories.

Category Table

====================
|  Category Name   |
====================
|  category1       |
|  category2       |
====================

Sub category Table

===========================================
| Owner Category   |  Sub category name   |
===========================================
|   category1      |  subc1               |
|   category1      |  subc2               |
|   category2      |  subc3               |
|   category2      |  subc4               |
===========================================

Now a user has access to subc1 if he has access to category1.

Is there any way to depict this scenario with E-R diagram so that by looking up diagram one can know this constraint?

Basically I want to know how this access constraint is propagated to next phase of designs.

Answer 1

Entity-Relationship diagrams show entities and relationships, including the cardinality of the relationships.

Some people and tools expand this to show attributes, primary keys and foreign keys. I've even seen some attempts at visually representing some contraints like attribute nullability and basic data domains. Some of the classic graphical conventions, like IDEF1X also include information about the nature of the relationships (whether they are determinants of the children or not).

I'm not aware of any graphical conventions for showing access rules in an ER diagram.

I would argue that trying to pack too much information into a visual representation of the schema will make the diagram so difficult to understand that it becomes unhelpful. I would say that this applies to something like access rules.

In order to show access rules, you really need to show the accessors (i.e. the users) since different accessors will generally have different access rules. There is no established convention for including users in an ER diagram so I wouldn't try to do it that way.

As an alternative, I would suggest using tables or charts to indicate which users have access to which entities. You could include a column that shows data-level rules that expand on the table-level rules. A data level rule could be something like "users can only access sub categories which belong to categories that they own".

It won't result in a pretty picture, but I believe that understandable documentation will beat a pretty picture when it comes to trying to explain your design to a developer or customer.