Database Diagram

[Terminum]

Hi, has anyone found/created an ERD diagram (or something similar) for the oscommerce database? The most recent one I've found is from 2004 and it seems to be out of date.

 

I have also tried creating a sql script and used the reverse engineering tool in MySQL workbench, but for some reason it only imported the tables and did not show relationships.

[MrPhil]

osC does not use foreign keys, so I'm not sure if your tool will realize that many IDs actually point to fields in other tables. If you change the .sql listing to declare all instances of *_id (that are not auto_increment and therefore local) to be foreign keys, your database tool might come up with some interesting relationships.

 

osC uses MySQL as its database, where the usual engine is MyISAM, which does not support foreign keys. If the InnoDB engine was commonly available (supports foreign keys), osC could have used that (but undoubtedly would require some recoding).

[Terminum]

osC does not use foreign keys, so I'm not sure if your tool will realize that many IDs actually point to fields in other tables. If you change the .sql listing to declare all instances of *_id (that are not auto_increment and therefore local) to be foreign keys, your database tool might come up with some interesting relationships.

 

 

Thanks, that's good to know. I'm new to mysql and didn't even realize you could have relationships without foreign keys. When pointing to regular fields, does this create ad hoc foreign keys of some sort? Can you link to a reference for how this is done properly? I think my book assumes you are using tables of the InnoDB type.

 

Since there are no foreign keys in this version, I'm inclined to try v3.0. Any thoughts on that?

 

Also if you know of any diagram representing these relationships for oscommerce that would be extremely helpful.

[MrPhil]

You could say that they're "ad hoc" foreign keys -- they should have the same value in two or more tables (one of which it is the primary key for). However, MySQL knows nothing about this relationship, and cannot enforce any restrictions, such as not allowing a record to be deleted until the other records in other tables (with a foreign key pointing to the deleted record) get deleted first. osC's PHP code has to take care of that in an ad hoc manner, since the database itself isn't aware of what happened. Sorry, I can't provide any more information or links (maybe someone else can).

 

If osC was using the InnoDB engine, it could enforce these foreign key relations. Perhaps some derivations of osC (code forks) do this? I have no idea if osC v3 will do this -- you might look around this forum for its "road map". I don't recall whether the database layer is being expanded to handle other databases, or if I'm thinking of another product. Even if that's the case, since it will still run on MySQL, my guess would be that foreign keys would not be used (too many different code paths to worry about).

 

I don't know of any diagram. As I mentioned before, if you modify the CREATE TABLE lists to declare *_id fields as "foreign key", your ERD tool might be able to diagram the relationships (but no promises).

[♥geoffreywalton]

There are a couple of links to ERDs in my useful threads link.

 

Don't know the dates though but not much has changes in the basic design.

 

G

[Terminum]

Thanks for the help, that's great.

 

Phil check this out:

http://blogs.oscommerce.com/2009/06/18/ensuring-database-integrity-with-foreign-keys/

 

 

I came across that article but I'm not sure I understand it. It says "This update allows MySQL to natively support foreign keys on InnoDB databases that are directly defined in the database schema, and uses a fallback mechanism for MyISAM databases where foreign key relationships are defined in a database table."

 

How does one determine if they are using InnoDB or MyISAM?

[MrPhil]

First of all, understand that native support for Foreign Keys will be in osC version 3, but don't ever expect it to show up in 2.2. I frankly don't see the point in adding such code, unless the plan is to drop support for MySQL with the MyISAM engine, and require (for MySQL users) the InnoDB engine. That could end up kicking a lot of stores off of shared servers. Otherwise, it's duplicate effort -- they still have to support code to manually handle stuff that MyISAM doesn't do (Foreign Keys), and have different code for databases with Foreign Key support. What's the point?

 

How does one determine if they are using InnoDB or MyISAM?

Your database can tell you, such as in phpMyAdmin or looking at a database dump (.sql file). Presumably there's an SQL query for asking what the database engine being used is, so that an application could choose the appropriate code branch.