OpenCart Transactional (aka InnoDB support)

Hi to all,
i've tried some of the opensource-CMS-Cart out there and found that opencart is one of the best available (well designed, well coded, nice UI).

BTW, the backend DB schema is not relational and this means that there's no sql-transaction support... too bad.
I don't want to annoy you with the danger using a non-relational schema or the multiple advantages of a relational schema... but a relational-schema is always a good point (specially for an e-commerce platform).

So i though if there's the possibility to change from non-relational to relational without too much effort.
And so, here's a list of things to do to accomplish this task:
1) switch MyIsam tables to InnoDB tables
2) build the relations between the existing tables and establish a good policy for the ON CASCADE & ON DELETE rules
3) check if the php-core of opencart can be changed to include a transactional mechanism and implement it.

In the points 1 & 2 the php-back-end should not be affected and these points already add a reliability layer on the CMS.
(Part 3 is the hardest part and i've not yet started to investigate)

So, here's the (5 june)-svn-Mysql-schema with relations added to it:
- http://www.mauropanzeri.netsons.org/ope ... _db_er.pdf (pdf for readability)
- http://www.mauropanzeri.netsons.org/ope ... _db_er.mwb (source file, use mysql-workbench to modify it: http://wb.mysql.com/).

I think i've made the majority of the needed connections and i only don't see how to connect some of the IDs (account_id, checkout_id) in the "store" table.

I hope this file will help people to understand the structure of the DB schema and that it'll start a new branch of the project with relational-DB support. (IDEA: open a branch on the official repository ?)

hoping that my bad english won't kill you, thanks 4 your attention!

PS: feedbacks are welcome!

--
edit: added pdf-version of the schema

Thank you for the info and idea. It is a good idea in the big picture, but definitely would take a lot of work at this point to implement. We will however keep this idea in mind

Thanks!

I created a sql file to transform the original 1.4.8-opencart-db-schema to InnoDB tables, adding the foreign keys and relations.
http://www.mauropanzeri.netsons.org/ope ... tional.sql

step to install:
-install opencart
-apply the sql on the opencart schema

some very little test around my localhost's installation show that there are no issues after the upgrade. But it requires absolutely more tests and some checks to see if all relations are right or they lack somewhere.

I was totally wrong, there are a number of issue related to the integration of foreign keys in the DB schema...

To be clear - does this mean that your update script is not a safe operation?

I'm trying to find an ERD diagram of the open cart database, in lieu of building my own. Seen one kicking around anywhere?

mwallace wrote:To be clear - does this mean that your update script is not a safe operation?

The update should be safe, but i cannot guarantee this, because there's no way to test until a complete test around the cart is done.
The things that don't work are many of the update/insert query around the cart-core, because they wasn't designed to support foreign keys. As Qphoria already noticed, modify the opencart's code to support them, it's a huge job.

mwallace wrote:I'm trying to find an ERD diagram of the open cart database, in lieu of building my own. Seen one kicking around anywhere?

So you can make it by yourself, or you can look at mine:
http://www.mauropanzeri.netsons.org/ope ... _db_er.pdf
i think It's pretty accurate, but either it could contain mistakes because no one reviewed it.

MySql Workbench do a good job. it's capable of reverse engineering an existent mysql-DB to an ER diagram (this way i've reverse-engineered the original schema) and VICEVERSA automatically create a designed/modified one (this way i've realized the update script).

Was an ERD ever made? I tried multiple times to create one with MySQL Workbench, but it didn't work. None of the tables were automatically joined.

The pdf mention above is no longer available.

Marc

Hi,
Can you please let me know if transactional mechanism and Relational schema - are being introduced in Opencart and when if any plans?

I think Opencart will be much better if it supports database Transactions.

Thank you.

You can download relational schema (1.5.1.3.1) from attachment:
http://forum.opencart-ua.org/viewtopic.php?f=4&t=250

hi everyone
I am new to opencart. So i m having difficulty in how the file are related to each other.............
So if anyone can help me by giving the flow diagram of file.....
Thanx in advance

I think InnoDB is a *must* for OpenCart database, and I cannot understand why it is left as MyISAM in the first place.

For example, I was testing the system and I have created a basket and I have never paid for the basket. On the stock report, even if I haven't completed the order, the order is counted as a completed order -actually this is a bug, I'll post the fix when I fix it- and the stock report is completely false. So I decided to delete the uncompleted order from the DB, but when I look at the DB I see that I have to delete rows from many tables. If the DB had been an InnoDB table, the deletion would have occured automagically when I deleted the order row. I can still delete the order row, but I will have no idea if there will be any orphan records left anywhere in the OpenCart DB.

When you switch to InnoDB, the code does not have to change and even if there is a need, the changes are very minor. I have tested the DB proposal found in the forums and the system seems to be working very smooth and problem free. Migrating can be a bit tricky though, since InnoDB will bark when you try to insert related tables into the DB.

As we all know InnoDB prevents wrong or orphan data entry, which creates a poka-yoke[1] both for the programmers and administrators. IMHO, InnoDB should be implemented in OpenCart, and it should have been implemented yesterday.

[1] http://en.wikipedia.org/wiki/Poka-yoke

sarmisak wrote:For example, I was testing the system and I have created a basket and I have never paid for the basket. On the stock report, even if I haven't completed the order, the order is counted as a completed order -actually this is a bug, I'll post the fix when I fix it- and the stock report is completely false.

There is no stock report in OpenCart and product levels aren't adjusted until checkout (check out the add function in /system/library/cart.php).

If the DB had been an InnoDB table, the deletion would have occured automagically when I deleted the order row. I can still delete the order row, but I will have no idea if there will be any orphan records left anywhere in the OpenCart DB.

I'm not very familiar with InnoDB. You're saying it will delete rows without an explicit command?

As we all know InnoDB prevents wrong or orphan data entry, which creates a poka-yoke[1] both for the programmers and administrators. IMHO, InnoDB should be implemented in OpenCart, and it should have been implemented yesterday.

Database issues are incredibly rare as far as the sorts of problems people have with OpenCart. The great thing is that OpenCart is open source so you're completely free to program or hire a developer to program any "must have" features for it.

Ryan,

That you don't often see database issues with Open Cart is nice to hear. I have worked a bit with Innodb tables, and looked at some of the issues involved in migrating to them as well.

I would not say that simply using the InnoDB engine would deliver any of the benefits described by sarmisak. What he is describing would seem to be the result of using transactions and triggers.

Transactions encapsulate a set of queries such that if one fails, the results of the entire set are rolled back and data integrity is maintained.

Triggers allow you to cascade an action from one table to another - so that if you delete an entry in one table which is referred to by a foreign key reference in another table the related entry or entries in the other table may also be deleted by the database server rather than by explicit PHP coding..

David

Hi
i am begginer in open cart, I want to create a form for employee registration and login. i create employee table in database, but how to access form data and save it in mysql.
If possible, Please help me.

Hi!

read the discussion and would like to know if InnoDB and a better database schema (at least 3rd normal form) has been considered to be implemented to OpenCart.
The database schema of OpenCart is impossible to use with PHP frameworks like Zend, also to integrate the OpenCart data with other applications is limited due to clumsy DB schema :-(

evimail wrote:The database schema of OpenCart is impossible to use with PHP frameworks like Zend,

Why is that?

also to integrate the OpenCart data with other applications is limited due to clumsy DB schema :-(

That's not a useful critique. It would be far better for you to point out changes or an alternate schema.

rph wrote:

evimail wrote:The database schema of OpenCart is impossible to use with PHP frameworks like Zend,

Why is that?

also to integrate the OpenCart data with other applications is limited due to clumsy DB schema :-(

That's not a useful critique. It would be far better for you to point out changes or an alternate schema.

Zend supports InnoDB and therefore transactions, cascade delete and update. This is not possible with MyIsam.

What comes to clumsy DB schema, for example (there are other places, just took one) the present Customer->Customer_group relationship: 1) limits the customers to only one group and b) binds strongly together Customer table information with Customer_group information that are better to keep separate.

The better solution would be Customer<-- Customer group -> Group. This would be nicely in third normal form and both Customer and Group data are separate.

Also I cannot understand why addresses are duplicated to Order table. More clear would be foreign key rows from Order table to Address table. The same applies to Customer data in Order table. Why not just one Foreign key field to Customer table?
And why is needed table Order_total when the order total value can be calculated programmatically or used the value that is already present in Order table.
The main problem: very much duplicated information and especially without cascade update/cascade delete prone to errors and messed-up data.
Sorry for critics, but I got a job where I have to make some modifications to customers existing OpenCart solution and would like to know if data structure improvements are planned in (near) future :-)

evimail wrote:Zend supports InnoDB and therefore transactions, cascade delete and update.

Well, that's not the same as requiring it.

What comes to clumsy DB schema, for example (there are other places, just took one) the present Customer->Customer_group relationship: 1) limits the customers to only one group

Why wouldn't it? Customer groups are used for price overrides. If a customer was a member of two groups which both had product discounts which one would it use? You could create some complex scheme for allowing certain customer groups to take precedent over others but the whole thing could just as easily be accomplished by creating a new customer group for the customer in the current system.

b) binds strongly together Customer table information with Customer_group information that are better to keep separate.

Not sure where you're getting that from.

Also I cannot understand why addresses are duplicated to Order table.

Because they can change or be deleted by the customer. It should be really obvious that all dynamic order information needs to be stored.

And why is needed table Order_total when the order total value can be calculated programmatically or used the value that is already present in Order table.

I'm not sure where you got the idea order_total was used for calculating the order cost. It's used for itemizing the parts of it and making that area of the cart expandable. This is Relational Databases 101 here.

The main problem: very much duplicated information

Where?

and especially without cascade update/cascade delete prone to errors and messed-up data.

Pure scaremongering. In all the years I've worked with OpenCart I haven't seen one corrupted database.

Well, thanks for reply. From your answers I understand that you are not familiar with database normalization subject and therefore don't understand what I'm talking about. Also the benefits of automatic transactions are obvious if you once get the idea of it.

Information duplication, example. In OpenCart you have customer name and contact data in two tables: Customer and Order. What happens if you need to change the e-mail - have to change it in at in two places making at least two update queries. When Customer data was only in one table and Order table would point to customer data with foreign key there would be no need for two updates, one cascade update would be enough.

evimail wrote:In OpenCart you have customer name and contact data in two tables: Customer and Order. What happens if you need to change the e-mail - have to change it in at in two places making at least two update queries.

Orders don't get dynamically updated nor would you want them to. They're historical information. What a customer changes their address to today has no bearing on the address you shipped their order to six months ago. This is the way all modern ecommerce platforms operate.