I have been taking an approach on small applications for some time now where I have a very similar data structure that might apply to more than one different type of resource. To avoid making redundant tables, I simply use a composite of a parent_id and a level/type to create a relationship between one table and many others.
For example, I am working on an application where a user has a certain level of access to an account that may be shared by other users. There are resources and modules that belong to this account (accounted for elsewhere in the database) that I want to restrict access to.
For example, the user may have read access to the account as a whole, but I may want to allow them write access to a resource under the account. Or the user may be able to access the account, but not have any read access to certain resources under the account.
All permissions in the entire system would effectively be stored in a single table like so:
Here parent_id and permission_level combined ultimately decide which resource the user is being granted access to where user_role defines what
Likewise, in the same application, I need to store several user-entered string attributes across different types of resources. My current thinking for this model is like this:
Here attribute_type determines what the attribute is (e.g. "name", "description", etc.) and parent_id combined with attribute_level define which resource this attribute is applied to.
To me, the benefit to this approach is reducing the redundancy of data where I am not creating new tables or adding new columns when I inevitably discover a new level I need to account for. Ultimately it's easy to change and easy to scale.
The disadvantages are that I cannot create a foreign key constraint with parent_id and multiple tables and I need to be 100% sure my SQL always addresses the level in conjunction with the parent_id.
Is this a valid design, or is there any inherent risk I am not seeing?