Using Models as Join Tables and/or Self Referential Joins?

Do you really want to limit your message tracking to just one user per user? In your example, if you'd have to have two fields, last_messaged_user_id and last_messaged_date on your User model. So your two user records would look like:

id name last_messaged_user_id last_messaged_date
1 A 2 2007-12-19
2 B 1 2007-12-19

You would never be able to track multiple messages this way. A better design, if I understand your problem (which I doubt), is to use a join table and a has_many :through. The join table would have a from_user_id, a to_user_id, a date/time, and could even have the message, in which case, you could call it messages. Even if you don't keep the message text here, my opinion is to not clutter up the user record with information not specifically related to the user. This often results in over-normalization, in which case I sometimes have to denormalize some things, but I'd rather go that way than to have chaotic tables/models.


I think I'm sort of seeing what you are trying to do. I would probably have a users table/model, a messages table/model, and a relationships table/model:

users have_many relationships
users have_many messages

relationships have_many users
messages have_many users

The relationships table would link the users together, and on that table you store the information specific to the relationship (distance, level of correspondence, etc). One user can be part of many relationships.

The messages table would be an instance of a message between two users and could have from_user, to_user, sent_at, subject, body, parent_message_id (for threading/replies).


Well, let me ask you this: Can User A be in only one relationship at a time? So if User A is in a relationship with User B, User C is out in the cold until User D comes along? If that's true, then no, you don't need a many to many. But I was going on the assumption that User A could have simultaneous relationships with any number of other users, in which case you need a many to many. Unless there is a gap in my knowledge, which there often is.


The current models does not make much sense. Without the requirements
it is not possible to help

your migration don't look correct then using through you should not
create_table :users_relationships, :id => false do |t|
just use
create_table :relationships_users


#1. What is the person1, person2 columns?
#2. What are the concepts in your domain?
#3. What is the use case?

It seems to me your current models are not having the right attributes or you are missing a key abstraction in your domain.