Donato Donato - 1 month ago 8
MySQL Question

Many to many relationship with individual fields of a record

I have a table called email_messages. I has columns

from
,
to
,
cc
,
bcc
. At the moment I have them stored as text fields, which contain serialized arrays of email addresses. But I want to extract the addresses into their own table, so I can reuse addresses and make it easier to search addresses with typeahead.

Obviously, I need a many to many. But if I do this:

class EmailMessage
has_many :email_message_addresses
has_many :addresses, through: :email_message_addresses
end

class Address
has_many :email_message_addresses
has_many :email_messages, through: :email_message_addresses
end


How would I be able to associate addresses for each individual
from
,
to
,
cc
,
bcc
field for a single record? The
from
field can contain 3 addresses, the
to
field can contain 5 addresses, etc.

Answer

Add an attribute to email_message_addresses called role, which can take on the values from, to, etc.

You will probably want to add some methods so that you can do the following syntax

@message = EmailMessage.find(...)
@message.to_addresses  # -> array of addresses in the "to" field
@message.cc_addresses  # -> array of addresses in the "cc" field
#  etc.