Adaptable Database Design

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.

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.

So, a simple structure will not be sufficient.

Apart 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.

That is the basic idea. They just created more tables for each data type such as int, decimal, datetime, text, etc.

This does not come without extra cost. You cannot use one simple SQL select query to get a list of products any more.

You cannot even use this type of select query:

Because we do not know the number of attributes and we cannot hard-code attribute IDs e.g. 1, 2 and 3.

In addition to the complexity of recording and managing simple attributes, the other issue is performance: 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.

You can come with marvelous database designs for the problem you are facing. But the first thing you need to think is:

How often will the system change and require adaptation and configuration?

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 and MongoDB.

Leave a Comment