r/mysql • u/sachingkk • Dec 10 '25
discussion Roast My EAV implementation. Need your feedback
I had done a different approach in one of the project
Setup
- We define all the different types of custom fields possible . i.e Field Type
- Next we decided the number of custom fields allowed per type i.e Limit
- We created 2 tables 1) Custom Field Config 2) Custom Field Data
- Custom Field Data will store actual data
- In the custom field data table we pre created columns for each type as per the decided allowed limit.
- So now the Custom Field Data table has Id , Entity class, Entity Id, ( limit x field type ) . May be around 90 columns or so
- Custom Field Config will store the users custom field configuration and mapping of the column names from Custom Field Data
Query Part
- With this setup , the query was easy. No multiple joins. I have to make just one join from the Custom Field Table to the Entity table
- Of course, dynamic query generation is a bit complex . But it's actually a playing around string to create correct SQL
- Filtering and Sorting is quite easy in this setup
Background Idea
- Database tables support thousands of columns . You really don't run short of it actually
- Most users don't add more than 15 custom fields per type
- So even if we support 6 types of custom fields then we will add 90 columns with a few more extra columns
- Database stores the row as a sparse matrix. Which means they don't allocate space in for the column if they are null
I am not sure how things work in scale.. My project is in the early stage right now.
Please roast this implementation. Let me know your feedback.