OpenCart 2.0 (Normalizing database / extensions)

This article will describe the basics of Database Normalization and how it can relate to your OpenCart Store. Each OpenCart store has a database whether you know it or not. For most, we just download the software, follow the installation instructions and start selling. You actually have to store data somewhere, hence your database. OpenCart does a pretty good job in normalizing data but what if you start adding custom extensions? Adding to the default schema could be disastrous if you do not know what you are doing. It’s easy to screw up the integrate of the data if you aren’t sure how to normalize new tables.What is database normalization?

Database normalization is the process of organizing the attributes and tables of a relational database to minimize data redundancy. There actually is another goal in normalization and that is to ensure that dependencies makes sense.

The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you’ll often see 1NF, 2NF, and 3NF along with the occasional 4NF. This article will not go into extreme detail about all of the forms but in general will describe the basics of what to do and what not to do.

In short, these guidelines build off of one another and act as a standard to follow. So, when installing a custom extension into Opencart 2.0 that either will alter the existing database with new tables, or create fields in existing tables , be sure to follow the guidelines.

What not to do

Say you are installing/creating a custom extension for your Opencart store that requires a relation to a particular product. Each product as a product ID to establish uniqueness within the RDMBS where the ID is never the same. You have some choices here on how you should go about doing this.

Add the special field to the existing product table. This will ensure that the special field will always be tied to the correct product ID at all times

Create another table and set up a relation between keys. This method is to set up your own table where there would be a field called product_id to establish a relation between tables.

Which is better? It really depends. If your new extension involves the product as only a SMALL part of the overall procedure then consider using a JOIN with your special table. This is when there are other things going on that have nothing to do with the product itself. A good example of how Opencart does this is the product_option table.

If the extension only involves a couple of fields that are ALL related to the product then don’t use a JOIN. JOINS can be expensive.

The wrong thing to do:

Very little affects performance like failing to apply an index or applying an index incorrectly. It isn’t rocket science, and there are guidelines that help. But many developers still avoid the task altogether. Without proper indexing, your database will eventually slow down and irritate users. Perhaps the only thing that causes as much trouble as no indexing is too much indexing.

There’s a lot of free information on indexing, but here are my simplest recommendations on the subject: