Is it valid design to relate different but similarly structured data to resources using composite keys?

Question

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:

account_permission
permission_id
parent_id
permission_level
user_id
user_role

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:

attribute
attribute_id
parent_id
attribute_level
attribute_type
attribute_value

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?

Answer 1

This smells like the EAV (Entity-Attribute-Value) anti-pattern, especially your second table attribute.

While it may make your work as a developer easier for designing and managing the tables, it also runs the risk of:

• Bad data integrity
• Unnecessary data redundancy
• Performance issues when queried
• Added complexity when trying to query on and / or manipulate certain types of data that are shoehorned into the value column

Please see the following for more information:

1. EAV - is it really bad in all scenarios?

2. Is there a name for this database schema of key values?

3. EAV - in an ecommerce case, is it really an anti-pattern?

4. Database Modelization Anti-Patterns

5. Anti-pattern: entity-attribute-value (EAV)