Is it OK to use "semantically reversed" associations, like Message blongsTo Attachment, in CakePHP?

Question

Suppose that we have the following partial ER diagram:

Notice that the attachments table will subsequently be used for the messages', submissions', assignments', and lectures' attachments.

The problem is with the 3 one-to-one relationships between attachments and messages, submissions, and assignments. According to CakePHP's conventions, Message belongsTo Attachment (and Attachment hasOne Message) because Message contains the foreign key (the same thing applies to the other 2 relationships). Of course, it makes more sense to say that Message hasOne Attachment (and that Attachment belongsTo Message). If we only had messages and attachments, it would be easy to "properly orient" the relationship by moving the foreign key to attachments.

But the problem again is that messages, submissions, assignments, and lectures have relationships with the same attachments table. One way to get the "semantically correct" relationships is to have 4 different Attachment models: MessageAttachment, SubmissionAttachment, AssignmentAttachment, and LectureAttachment.

Assuming we are only interested in retrieving the attachment of a certain message, submission, or assignment, is it OK to use these semantically reversed associations, or should we properly orient them by separating Attachment into 4 different models as mentioned above?

Answer 1

By semantics you also mean the natural semantics of the language. But you are talking about a particular, technical scenario that holds a different semantics.

That's perfectly fine. These are just names. If you have A and B (attachments and messages), and in your case B hasOne A, then it is the right thing to say attachment hasOne message.

Answer 2

Warning: this answer is a bit along the lines of "you should be able to do this with an MVC framework". I am unfamiliar with CakePHP, and briefly looking at the documentation I can't figure out how to actually do the following.

In Perl DBIx::Class (which I am familiar with) it is possible to impose additional constraints in belongs to relationships than just fk matching. I would solve this problem by adding a field type to attachment, which indicates the type of object (e.g., message, lecture) that owns this particular attachment and then give attachment a foreign_id.

The query syntax would be SELECT * from attachment WHERE foreign_id == myId AND type == MyType.

It looks like that structure would let you eliminate the attachments_lectures table, since that seems to just be letting you have many attachments per lecture. You would also get for free being able to have many attachments for assignments, submissions, and messages if you were so inclined.