I need to save incoming and outgoing emails from an application in a MySQL database server.
It used to be simple to do in that all that needed to be tracked was the from address, to address, subject, and body, but now I need to support multiple to addresses and cc addresses in a single email.
I am struggling with the ideal schema (or structure) and query needed to do this the most efficiently. I started thinking of having one table for the email, another for the to addresses, associated back as a many-to-one relationship, and another table for the cc addresses also associated back with a many-to-one relationship and simply running three separate read queries to get the email, to addresses, and cc addresses to display on the front-end when the saved email gets opened.
Is this the most efficient way to achieve this at this point or is there a better and more efficient solution?