Marc, First, you should listen to Bob. ALWAYS listen to Bob. He is a smart guy and his advice is sound.
If you are satisfied with Bob's answer, you may stop reading here. If you are interested in a more detailed answer, I'll elaborate. Still reading? Don't say I didn't warn you. Anyway... As far as I'm aware, Transfer supports both the MyISAM and InnoDB storage engines in MySQL I'm sure Mark would have documented it on on the Supported Systems <http://docs.transfer-orm.com/wiki/Supported_Systems.cfm> page if it were otherwise. Transfer does not *require *the database engine to enforce relationship constraints. MyISAM does not support such constraints, so placing such a requirement would force Transfer to drop MyISAM support (which I believe is still fairly popular in the MySQL world). Transfer does provide some small help with database integrity. The design of the Transfer API helps avoid certain types of programming errors that tend to relationship violations. In particular, it does not allow you to assign values to Foreign Key (FK) columns directly: you must instead assign the relationships between objects, and the ORM then sets the FK column values for you. This way, your code is less likely to put a person ID value in a department ID column. However, Transfer does *not *enforce relationship constraints and does not catch *all* possible programming errors that could lead to relationship violations. I have done all my professional Transfer work on MS-SQL with constraints on all my relationships enabled, and on a number of occasions during my development MS-SQL threw a constraint violation exception. Each time, the exception revealed a bug in my code that may otherwise have gone unnoticed until much later ... perhaps not until after deployment to production (HORROR!). I've had to deal with messes created in production databases that had few or no constraints defined, and I'd rather not go through that again if I could avoid it. So getting back to what Bob said, using relationship constraints is strongly recommended even though it's not a strict requirement of Transfer. However, I disagree with Bob's last point. Setting up relationship constraints in the DB does *not *automatically result in better performance; in fact it can have the opposite effect in some cases. Not all DB systems automatically create supporting indexes with relationships, and not all supporting indexes improve query performance. It often depends on the DB engine being used, the relationship, and the size and cardinality of the data set. For instance, a query on a many-to-one relationship where the number of rows in the "child" table is small does not benefit from a FK index. If you are concerned about performance you should do some kind of performance testing and bring in some SQL expertise if necessary. I think it's best that I stop here before I get any deeper into RDBMS design and relational database theory :-) Cheers, -- Dennis On 20 January 2011 06:08, Mark Mandel <[email protected]> wrote: > Ditto on what bob said. > > Mark > > Sent from my mobile device. > On 20/01/2011 12:38 AM, "Bob Silverberg" <[email protected]> wrote: > > I could be wrong about this, but I don't think you _need_ to do it, on > > the other hand you probably _should_ do it. If you configure Transfer > > properly and write your code correctly then you yourself will be > > ensuring the integrity of the data, but wouldn't it be safer to allow > > the database to also do that for you? Also, unless you create indexes > > you'll be sacrificing performance. > > > > Bob > > > > On Wed, Jan 19, 2011 at 7:33 AM, marc <[email protected]> wrote: > >> Hi, > >> > >> As the subject reads - do I need to convert the tables to innodb, make > >> indexes and define relationships in my database (MySQL) when I use > >> Transfer? Or is integrity of data taken care of by Transfer. > >> > >> Marc > >> > -- Before posting questions to the group please read: http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer Try out the new Transfer ORM Custom Google Search: http://www.google.com/cse/home?cx=002375903941309441958:2s7wbd5ocb8 You received this message because you are subscribed to the Google Groups "transfer-dev" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/transfer-dev?hl=en
