3 ms·

>The proper EAV normalised structure would have three tables But then it's not EAV. In this case he is creating a single table to hold all of the attributes. T

by MSM 9y ago

>The proper EAV normalised structure would have three tables But then it's not EAV. In this case he is creating a single table to hold all of the attributes. The pros of this model are that once the business decides they also want to record which browser was used, there are no database changes required. In your case, you'd have to spin up a new table (and then another one once they want to store the time, and another where they want to store whether this is a returning customer, etc.) I'm not suggesting EAV is the correct solution, but the model has its merits... rarely :).

barbegal 9y ago
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.

by MSM 9y ago

barbegal 9y ago

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.