4 ms·

Maybe I haven't understood the article fully but I don't understand the use case here. Showing data in columns vs rows is purely about representation of the dat

Maybe I haven't understood the article fully but I don't understand the use case here. Showing data in columns vs rows is purely about representation of the data as opposed to how it is stored in a database. The first query with one value per row seems like a perfect way of representing the data in the database. In addition, the author claims that very poor performance comes from using an EAV model due to joins. In reality with the right indexes, joins can be very high performance and an EAV model can give you better performance than de-normalised tables. Performance degradation often comes from attempting to pivot the data into a de-normalised structure. Take the example the author uses where the attributes are name, email and website. The proper EAV normalised structure would have three tables, one for name, one for email and one for website. With the three tables you can enforce the constraint of one value per entity so you don't have to use the MAX hack and the joins between the tables will be more performant than joins on a single table. With this database structure, lots of operations are more performant than using the de-normalised table. For a given submission ID we can get the name, email, website (if they exist) faster by using three queries in parallel. Each individual query will operate faster because the index for each table will be smaller than the index for the de-normalised table.

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.
mirimir 9y ago
I agree. This is all about display of the data. However, the point of pivots is getting a two-dimensional display of the data. Using the example from the guide, a simple aggregate query would give you: Year Month Revenue 2016 01 1 2016 02 23 2016 03 345 ... ... ... 2017 01 567 2017 02 1234 2017 03 4567 ... ... ... That's fine, but for some readers, it's hard to see year-to-year changes. With a pivot, you get: Revenue / Month Year 01 02 03 ... 2016 1 23 345 ... 2017 567 1234 4567 ... ... ... ... ... ... Also, for what it's worth, I gotta say that Excel does a great job at pivoting. Better than MySQL, anyway, running on the same hardware. I've created pivots with thousands of columns.

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.

mirimir 9y ago

I agree. This is all about display of the data. However, the point of pivots is getting a two-dimensional display of the data. Using the example from the guide, a simple aggregate query would give you: Year Month Revenue 2016 01 1 2016 02 23 2016 03 345 ... ... ... 2017 01 567 2017 02 1234 2017 03 4567 ... ... ... That's fine, but for some readers, it's hard to see year-to-year changes. With a pivot, you get: Revenue / Month Year 01 02 03 ... 2016 1 23 345 ... 2017 567 1234 4567 ... ... ... ... ... ... Also, for what it's worth, I gotta say that Excel does a great job at pivoting. Better than MySQL, anyway, running on the same hardware. I've created pivots with thousands of columns.