3pc design patterns & modx database design philosophy?

A design question for modx team. Im trying to mimic the standards/design philosophy modx Revo uses in a 3pc Im designing:

I notice there are no relations, or foreign keys in the revo db tables. I was wondering why that’s so? In my many years of mickeysoft and oracle programming, almost all databases I have seen or been involved with, have FKs. Just curious what the driving philosophy behind the absence of foreign keys is?

I assume it’s because your using mysql as a ’object’ database of sorts, and not as a traditional ’relational’ db.

(I’d like to create a 3pc that fully generates the xml schema files including all relationships, but that would definitely depend on the presence of FKs in the db tables, and a dtd or xsd for the schema xml file. )


Dave

It’s simply because the application we were building this for used MySQL’s MyISAM db engine exclusively, which does not support FK constraints. Thus, xPDO implements the relationship constraints in the application code. It can be easily modified/extended to support FK constraints however. If you would do the honors to create a feature request for the xPDO project in JIRA to support reverse-engineering of InnoDB FK constraints for the mysql driver. This is driver specific BTW, and in the SQLite3 driver for xPDO I am working on, the FK constraints will be reverse-engineered, so again, this was simply a matter of designing to the lowest common-denominator at the start, which was MySQL’s MyISAM engine.

what is a "3pc lm design"???

Quote from: charliez at Apr 29, 2010, 10:34 AM

what is a "3pc lm design"???

translation: "A third-party component I am designing"

Quote from: OpenGeek at Apr 29, 2010, 10:19 AM

It’s simply because the application we were building this for used MySQL’s MyISAM db engine exclusively, which does not support FK constraints.

Thanks Jason. No fk’s allowed in the myisam engine, would account for none being present in the design.( Hmmm. Lack of FKs in a relational database engine’s design? I’ll google that one. )

Wow, you guys continue to amaze me with how quickly you fix modx issues (I’m thinking the session_write_close bug on windows) and take new feature requests.

Sorry for my lack of basic mysql knowledge. two more quick followup questions:

1. Can I run my custom 3pc tables with the innodb engine, even if they exist inside the modx db? Or, would I have to create a separate database outside of the modx db to get FKs and Innodb?

2. Would you recommend designing 3pc tables with, or without, FKs as a general rule of 3pc development?


I’ll make the JIRA feature request later this afternoon. Thanks much for the offer.

--------------------------------

To answer my own question #1: yes you can run a different driver on a per table basis in mysql. More info here: http://www.kavoir.com/2009/09/mysql-how-to-change-or-convert-myisam-to-innodb-or-vice-versa.html


This page sums up MyISAM vs. INNOdb MySQL engines, short and sweet. In a nutshell, MyISAM is faster, uses less resources, and is much more scalable: http://www.kavoir.com/2009/09/mysql-engines-innodb-vs-myisam-a-comparison-of-pros-and-cons.html