I had an opportunity to work on a Magento implementation and checked its database. I had heard about its adaptability to almost any online e-commerce business in almost any country. So, I had a chance to see how it is achieved on the database level.<\/p>\n
For example, when we want a system which is adaptable and easy to configure, even a simple table of products becomes a headache because we do not know what will be stored exactly per business and per country, never mind the multilingual requirements.<\/p>\n
So, a simple structure will not be sufficient.<\/p>\n
\r\nCREATE TABLE product\r\n(\r\n product_id int,\r\n prd_code varchar(20), \r\n prd_name varchar(100),\r\n prd_desc text,\r\n prd_price float,\r\n prd_avail_from date,\r\n prd_avail_to date,\r\n category_id int\r\n);\r\n<\/pre>\nApart from few very common attributes, we have so many unknowns. One solution to the problem is, like Magento developers did, is to create separate tables to define attributes and to store those attributes for each product.<\/p>\n
\r\nCREATE TABLE attribute\r\n(\r\n attribute_id int,\r\n atr_name varchar(30)\r\n);\r\n\r\nCREATE TABLE product_attribute\r\n(\r\n product_id int,\r\n attribute_id int,\r\n atr_value varchar(255)\r\n);\r\n<\/pre>\nThat is the basic idea. They just created more tables for each data type such as int, decimal, datetime, text, etc.<\/p>\n
This does not come without extra cost. You cannot use one simple SQL select query to get a list of products any more.<\/p>\n
\r\nSELECT product_id, attribute_1, attribute_2, attribute_3\r\nFROM product\r\n<\/pre>\nYou cannot even use this type of select query:<\/p>\n
\r\nSELECT product_id\r\n (SELECT pa1.atr_value FROM product_attribute pa1\r\n WHERE pa1.product_id = p.product_id AND pa1.attribute_id = 1) as attribute_1,\r\n (SELECT pa2.atr_value FROM product_attribute pa2\r\n WHERE pa2.product_id = p.product_id AND pa2.attribute_id = 2) as attribute_2,\r\n (SELECT pa3.atr_value FROM product_attribute pa3\r\n WHERE pa3.product_id = p.product_id AND pa3.attribute_id = 3) as attribute_3,\t\r\nFROM product p\r\n<\/pre>\nBecause we do not know the number of attributes and we cannot hard-code attribute IDs e.g. 1, 2 and 3.<\/p>\n
In addition to the complexity<\/strong> of recording and managing simple attributes, the other issue is performance<\/strong>: the database is hammered with so many extra queries. That is why the developers create additional background processes to tidy up the database and re-index it regularly. That means the server will need more memory, faster CPUs and faster disks.<\/p>\nYou can come with marvelous database designs for the problem you are facing. But the first thing you need to think is:<\/p>\n
How often will the system change and require adaptation and configuration?<\/p>\n
If your answer is “rarely” or “maybe, once a year” then you need to spend more time on the design, even re-consider the database management system you are using. Nowadays, there are new ones for this purpose such as: CouchDB<\/a> and MongoDB<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"I had an opportunity to work on a Magento implementation and checked its database. I had heard about its adaptability to almost any online e-commerce business in almost any country. So, I had a chance to see how it is achieved on the database level.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[79,123,80,197,33],"class_list":["post-341","post","type-post","status-publish","format-standard","hentry","category-technology","tag-couchdb","tag-databases","tag-mongodb","tag-nosql","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/341","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=341"}],"version-history":[{"count":9,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/341\/revisions"}],"predecessor-version":[{"id":996,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/341\/revisions\/996"}],"wp:attachment":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=341"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=341"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=341"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}