Hi Ryan: With your proposed design, you can control the Content "isOnlyOneOf" using the beforeSave(), but consider the following:
1) What if, in the future, you want to create another specialized Content class? That means you will need to not only create a new table, but also add another column to the contents table. Changing the table structure when you've already accumulated a lot of rows can take a while, and will require a table lock. 2) Since the Content will generally be accessed in the context of the specialized class, each of the 'specialized_id' fields should be indexed; without these individual indexes, the database will need to execute a table scan to find the appropriate Content matching the selected Bookmark/Message/Post, and can take longer as the contents table grows. The more indexes a table has, the more overhead involved when changing the rows (INSERT/UPDATE/DELETE), since it needs to update the associated indexes as well (even NULL values might be processed by the database when updating the indexes, see: http://stackoverflow.com/questions/289001/does-mysql-index-null-values). Depending on the database vendor implementation, as well as the storage medium for the tables (MySQL allows MyISAM, and InnoDB as some of the options), this could have implications, such as a full table lock for the duration of the DML (delaying other users from making changes to other rows in the contents table until complete) or increased storage requirements. Updating a single 2-column index *MAY* be more efficient than multiple 1-column indexes. To address your concern about wasted space, one variation to the model/ model_id design is to define the model field as an integer instead of a string. Your Content class can define constants to map the values to the appropriate model names (see: http://www.dereuromark.de/2010/06/24/static-enums-or-semihardcoded-attributes/). Another alternative if you are using MySQL, is to define the column as enum; note, however, that CakePHP doesn't directly support enum datatypes, and is discouraged by some programmers because it prevents the app from being "portable". Using the enum datatype also has the same disadvantage as item #1 above -- you need to change the table structure if you want to add additional values. Regarding foreign keys with the model/model_id design: I haven't tried this, and it may introduce other problems, but... CREATE VIEW content_models AS SELECT id AS model_id, 'Bookmark' AS model FROM bookmarks UNION SELECT id AS model_id, 'Message' AS model FROM messages UNION SELECT id AS model_id, 'Post' AS model FROM posts; ALTER TABLE contents ADD FOREIGN KEY fk_content_model (model_id, model) REFERENCES content_models (model_id, model); You should carefully test this because a) you cannot create an index on a view (at least, not in MySQL). Hopefully, by using model_id as the FIRST column in the foreign key clause, it would utilize the primary key of the underlying tables to access the reference quickly and b) foreign key constraints add overhead like indexes do, as each row in an INSERT/UPDATE/DELETE statement has to be validated against each constraint, blocking other access to the table until it completes. I have my preferences, but am open to other suggestions on design patterns addressing this issue, as well as other advantages/ disadvantages/clarifications of these patterns. On Feb 12, 10:42 am, Ryan Schmidt <google-2...@ryandesign.com> wrote: > I've heard that recommended before. Is that really the best practice? (I > honestly don't know, and am trying to learn.) It surprises me, because it > means that I cannot (in my database engine) declare a foreign key on the > model_id column, which means I lose the benefits that in-database foreign key > definitions provide. It also seems to waste space, storing the model name > string over and over again in every record. > > If I were designing this, I think my contents table would have fields > bookmark_id, message_id and post_id, each of which is defined as allowing > NULL, with the intention that only one of these be filled for any given > record; I'd have a beforeSave() method in the Content model ensuring that. > Does that make sense, or is there a reason why I wouldn't want to do it that > way? -- Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions. To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php
