6 ms·

Ah, Magento. It's "smart" EAV[0] Database schema gave my colleagues so much good time. I do not know how it became in more recent versions, but at the time I d

Ah, Magento. It's "smart" EAV[0] Database schema gave my colleagues so much good time. I do not know how it became in more recent versions, but at the time I distinctly remember them dealing with abysmal DB performances and completely gibberish auto-generated queries. In one case, I had to manually dig through the DB and write an ad-hoc query to extract data that was needed for reporting, otherwise the software would start doing a gazillion of round trips between a DB lookup, an AS-level computation, another DB lookup... That DB design tasted of super-mega-premature optimization, in which the developers wanted to guarantee extensibility at all costs, foregoing in the process the design of an actual data model. [0] https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80...

devy 8y ago
They had to adopt EAV database schema model because they want to design a schema that can be as generalized and flexible as possible. Obviously performance is the trade off. As a result, every Magneto shop has to use multiple layers of caching, which complicates the maintenance of Magneto, an open-source ecommerce off-the-shelf platform that's supposed to be easier than homebrew your own, ironically.
meritt 8y ago
Avoiding the EAV model while still being flexible (e.g. sparse attributes for any variety of products) is one place where the JSON datatype really shines. Throw your non-standard attributes into a JSON object and use the native DB functions to filter accordingly. In Postgres and MySQL 8 you can add conditional/functional/virtual indexes to keep things speedy. Maybe Magento will one day take advantage of these features. It's basically a modern-day approach to Serialized LOB: https://martinfowler.com/eaaCatalog/serializedLOB.html https://martinfowler.com/eaaCatalog/serializedLOB.html
- jimnotgym 8y ago
  That is great advice. Where you are talking about just allowing multiple attributes for a product catalogue you can also do 'EAV light' and have a 'user-defined-attribute' table that goes Sku, attribute_id, value and another table with a simple list of attributes. I have seen this in production (not in Magento) for a product catalogue and performance was satisfactory, queries were fairly simple and maintenance was easy. I think I prefer your JSON solution but this keeps very much in line with standard relational thinking.
jimnotgym 8y ago
> I do not know how it became in more recent versions It's the same. Expensive fast servers, lots of caching and the upkeep that goes with it.
- apple4ever 8y ago
  Yeah... but for a online store with a lot of products, its the best there is for its price point. Anything higher is LOADS of money.
  - mageno 8y ago
    I first tried Magento back in 2008. Past 2000 products it was unusable. We needed to have 50000 products. Magento was out and we built our own store. So what is "lots of products" now ?
    - apple4ever 8y ago
      We have about 20,000 styles and 150,000 SKUs (that's just the active list).
      - mageno 8y ago
        Wow... I wouldn't have bet on this 10 years ago.
tomc1985 8y ago
My first impression looking at their schema was, "what the fuck? if they wanted MongoDB why the fuck didnt they just go with MongoDB?" Thanks to Magento I can add "someone tried to turn MySQL into a typeless K-V document store" to the list of crazy things that I've seen.
- elvinyung 8y ago
  Honestly, I'm just surprised this is the first time you've seen something like that. It feels like I've seen it everywhere :P
  - mkolodny 8y ago
    I don't know if this is still the case, but at least in 2010 Reddit used Postgres as a key-value store [0] [0] https://kev.inburke.com/kevin/reddits-database-has-two-tables/ https://kev.inburke.com/kevin/reddits-database-has-two-table...
    - tomc1985 8y ago
      Postgres has a bunch of compound data types that make that possible... MySQL's 'version' a la Magento is the EAV design from hell
- conradfr 8y ago
  Magento is older than MongoDB and also I guess they wanted to rely on the ubiquitous Php/MySQL stack to get adoption. I think a better alternative would be Postgresql which can do relational sql and what MongoDB is supposedly good for.
  - bigiain 8y ago
    memcached had already been around for years before Magento - if they needed an example of how to do a key/value store. We we using that back when it was a LiveJournal Perl project, probably late '90s. There were a bunch of "persist memcached to disk" projects too - from memory we used a Java thing called Prevlayer or something... Having said that, I too mis-used MySQL as a key/value and EAV store back then, because it was the pragmatic choice at the time. If some of my work from the late '90s and early 2000s had grown into platforms still being used today - people would probably be questioning my sanity for choices like that as well...
neya 8y ago
Last year, I started writing my own E-Commerce solution after being frustrated with Shopify [1], I obviously referred to some well established projects to design on top of. The first one was WooCommerce (PHP) and the second one was Magento. After digging up a little bit [1], I was convinced that the way these solutions made money was through complexity. The more complex the solution is, the more customization that's involved. Believe me, when I tell you that your own E-Commerce solution built on top of rails/node/phoenix/etc. will be much better than these open source OTS solutions that have several hidden gotchas. IF at all I'd recommend something, it'd be Spree. [1] I'm writing an article on this (WIP): https://medium.com/build-ideas/my-journey-of-writing-an-e-commerce-engine-from-scratch-wip-ff4adb54c352 https://medium.com/build-ideas/my-journey-of-writing-an-e-co...
- pixelbash 8y ago
  Funny you should mention this, I make my living building sites with Shopify, Craft Commerce and Spree. After experience with Magento, Woo Commerce, Open Cart and many others these are the ones that give me the fewest headaches on a regular basis. ...Not to say they are perfect. Spree has an absolutely crazy order system[1] and Shopify is a pain to extend. Strangely the Venn diagram of platforms that are extendible/fun to code and the ones with a great community/ecosystem don't seem to have much overlap. [1] https://github.com/spree/spree/blob/3-5-stable/core/app/models/spree/order.rb https://github.com/spree/spree/blob/3-5-stable/core/app/mode...
  - 1123581321 8y ago
    Can you talk more about your Craft Commerce experience? I really like the CMS but I’m nervous about Commerce because there are so few integration plugins.
    - pixelbash 8y ago
      Sure, I tried it over Shopify because I wanted the ability to have complex pricing (well over 100 per product). I built a system that allowed for the ability to have dynamic prices based on the base product price + a combination of selected options. Craft has a fairly sane way to do plugins. Commerce itself is a well written if sometimes obtuse plugin, which is handy because some of the documentation isn't great (the new version - 3 - looks better). It is an EVA database, but it's simple to navigate and debug. There's some fairly powerful ways to handle loading content. In summary I'd say have a poke around the commerce plugin, you can run it for free unlicensed. It has a growing ecosystem of plugins, which is nice coming from Spree. Oh and support is pretty good too.

devy 8y ago

They had to adopt EAV database schema model because they want to design a schema that can be as generalized and flexible as possible. Obviously performance is the trade off. As a result, every Magneto shop has to use multiple layers of caching, which complicates the maintenance of Magneto, an open-source ecommerce off-the-shelf platform that's supposed to be easier than homebrew your own, ironically.

meritt 8y ago

Avoiding the EAV model while still being flexible (e.g. sparse attributes for any variety of products) is one place where the JSON datatype really shines. Throw your non-standard attributes into a JSON object and use the native DB functions to filter accordingly. In Postgres and MySQL 8 you can add conditional/functional/virtual indexes to keep things speedy. Maybe Magento will one day take advantage of these features. It's basically a modern-day approach to Serialized LOB: https://martinfowler.com/eaaCatalog/serializedLOB.html https://martinfowler.com/eaaCatalog/serializedLOB.html

jimnotgym 8y ago
That is great advice. Where you are talking about just allowing multiple attributes for a product catalogue you can also do 'EAV light' and have a 'user-defined-attribute' table that goes Sku, attribute_id, value and another table with a simple list of attributes. I have seen this in production (not in Magento) for a product catalogue and performance was satisfactory, queries were fairly simple and maintenance was easy. I think I prefer your JSON solution but this keeps very much in line with standard relational thinking.

jimnotgym 8y ago

> I do not know how it became in more recent versions It's the same. Expensive fast servers, lots of caching and the upkeep that goes with it.

apple4ever 8y ago
Yeah... but for a online store with a lot of products, its the best there is for its price point. Anything higher is LOADS of money.
- mageno 8y ago
  I first tried Magento back in 2008. Past 2000 products it was unusable. We needed to have 50000 products. Magento was out and we built our own store. So what is "lots of products" now ?
  - apple4ever 8y ago
    We have about 20,000 styles and 150,000 SKUs (that's just the active list).
    - mageno 8y ago
      Wow... I wouldn't have bet on this 10 years ago.

tomc1985 8y ago

My first impression looking at their schema was, "what the fuck? if they wanted MongoDB why the fuck didnt they just go with MongoDB?" Thanks to Magento I can add "someone tried to turn MySQL into a typeless K-V document store" to the list of crazy things that I've seen.

elvinyung 8y ago
Honestly, I'm just surprised this is the first time you've seen something like that. It feels like I've seen it everywhere :P
- mkolodny 8y ago
  I don't know if this is still the case, but at least in 2010 Reddit used Postgres as a key-value store [0] [0] https://kev.inburke.com/kevin/reddits-database-has-two-tables/ https://kev.inburke.com/kevin/reddits-database-has-two-table...
  - tomc1985 8y ago
    Postgres has a bunch of compound data types that make that possible... MySQL's 'version' a la Magento is the EAV design from hell
conradfr 8y ago
Magento is older than MongoDB and also I guess they wanted to rely on the ubiquitous Php/MySQL stack to get adoption. I think a better alternative would be Postgresql which can do relational sql and what MongoDB is supposedly good for.
- bigiain 8y ago
  memcached had already been around for years before Magento - if they needed an example of how to do a key/value store. We we using that back when it was a LiveJournal Perl project, probably late '90s. There were a bunch of "persist memcached to disk" projects too - from memory we used a Java thing called Prevlayer or something... Having said that, I too mis-used MySQL as a key/value and EAV store back then, because it was the pragmatic choice at the time. If some of my work from the late '90s and early 2000s had grown into platforms still being used today - people would probably be questioning my sanity for choices like that as well...

neya 8y ago

Last year, I started writing my own E-Commerce solution after being frustrated with Shopify [1], I obviously referred to some well established projects to design on top of. The first one was WooCommerce (PHP) and the second one was Magento. After digging up a little bit [1], I was convinced that the way these solutions made money was through complexity. The more complex the solution is, the more customization that's involved. Believe me, when I tell you that your own E-Commerce solution built on top of rails/node/phoenix/etc. will be much better than these open source OTS solutions that have several hidden gotchas. IF at all I'd recommend something, it'd be Spree. [1] I'm writing an article on this (WIP): https://medium.com/build-ideas/my-journey-of-writing-an-e-commerce-engine-from-scratch-wip-ff4adb54c352 https://medium.com/build-ideas/my-journey-of-writing-an-e-co...

pixelbash 8y ago
Funny you should mention this, I make my living building sites with Shopify, Craft Commerce and Spree. After experience with Magento, Woo Commerce, Open Cart and many others these are the ones that give me the fewest headaches on a regular basis. ...Not to say they are perfect. Spree has an absolutely crazy order system[1] and Shopify is a pain to extend. Strangely the Venn diagram of platforms that are extendible/fun to code and the ones with a great community/ecosystem don't seem to have much overlap. [1] https://github.com/spree/spree/blob/3-5-stable/core/app/models/spree/order.rb https://github.com/spree/spree/blob/3-5-stable/core/app/mode...
- 1123581321 8y ago
  Can you talk more about your Craft Commerce experience? I really like the CMS but I’m nervous about Commerce because there are so few integration plugins.
  - pixelbash 8y ago
    Sure, I tried it over Shopify because I wanted the ability to have complex pricing (well over 100 per product). I built a system that allowed for the ability to have dynamic prices based on the base product price + a combination of selected options. Craft has a fairly sane way to do plugins. Commerce itself is a well written if sometimes obtuse plugin, which is handy because some of the documentation isn't great (the new version - 3 - looks better). It is an EVA database, but it's simple to navigate and debug. There's some fairly powerful ways to handle loading content. In summary I'd say have a poke around the commerce plugin, you can run it for free unlicensed. It has a growing ecosystem of plugins, which is nice coming from Spree. Oh and support is pretty good too.