3 ms·

I see what you're saying, I think my definition of EAV is probably not the correct one. In a real EAV where you have just one table and you only change data not

I see what you're saying, I think my definition of EAV is probably not the correct one. In a real EAV where you have just one table and you only change data not the table structure you can only really query for a list of attributes. In that case there is no performant way of querying for a specific attribute. The value of this style of database seems limited because you have to change the application in order to make good use of the new attribute you've added but you refuse to change the database at the same time. The best systems are where application and database match; either both contain knowledge of an attribute or neither contain knowledge of an attribute. The case of neither having knowledge is only useful where an outside party (such as the end user) alone can interpret the attribute. For example listing device specs where the user alone can compare them and not the application like: backlight = LED, subpixel = PenTile, power-supply = switch-mode.

ianmcgowan 9y ago
Your model is perhaps EV - you don't need the attribute if the data is in separate tables? It seems like normalization on steroids - I was going to joke about 6NF, but turns out that's a thing: https://en.wikipedia.org/wiki/Sixth_normal_form https://en.wikipedia.org/wiki/Sixth_normal_form. I'm one of today's lucky 10000! One thing EAV is really good for is allowing for user-defined attributes - some privileged users are allowed to add a new user defined field, and there's a UI that allows regular users to view and maintain them. A pain for reporting, but you can either update a view as UDF's are added or drop and recreate a materialized view at set intervals. I've worked with applications where there's a user-defined screen for each entity in the system, it can be a great get-out-of-jail card for a lot of late/ad-hoc requests.

ianmcgowan 9y ago

Your model is perhaps EV - you don't need the attribute if the data is in separate tables? It seems like normalization on steroids - I was going to joke about 6NF, but turns out that's a thing: https://en.wikipedia.org/wiki/Sixth_normal_form https://en.wikipedia.org/wiki/Sixth_normal_form. I'm one of today's lucky 10000! One thing EAV is really good for is allowing for user-defined attributes - some privileged users are allowed to add a new user defined field, and there's a UI that allows regular users to view and maintain them. A pain for reporting, but you can either update a view as UDF's are added or drop and recreate a materialized view at set intervals. I've worked with applications where there's a user-defined screen for each entity in the system, it can be a great get-out-of-jail card for a lot of late/ad-hoc requests.