I’m struggling with a database design decision. I’m hoping an Ecto expert can set me on the right path.
My app will allow users to create Groups and Events. The user can then create Template messages associated with group membership and event activities. A template message is like: “Welcome to the group. We meet on Mondays, blah blah blah.”
So I have the following ** simplified for discussion **:
Group Table [References just 3 templates]
welcome_member_template_id (references a Template id)
Event Table [References just two templates]
reminder_template_id (references a Template id)
I had no problem defining the migrations:
def change do alter table(:groups) do add :welcome_msg_id, references(:templates) add :inactive_msg_id, references(:templates) add :delete_msg_id, references(:templates) end end
But I’m confused about how to set up the schema for Groups and Templates. Can I use Belongs_to even if some Templates will belong_to Groups and some Templates will belong_to Events?
Will this work?
schema "templates" do ... belongs_to :group, Groups belongs_to :event, Events end schema "groups" do field :welcome_msg_id, :integer field :inactive_msg_id, :integer field :delete_msg_id, :integer has_many :templates, Template end schema "events" do field :reminder_template_id, :integer field :some_other_template_id, :integer has_many :templates, Template end
Those Schemas definitely looks wrong to me. I just need Group and Events to be able to point to a couple of Templates. Join tables seem like overkill.
I guess my other option is to load up the Template table so that it looks like this:
template_type (“group” or “event”)
message_type (“reminder”, “welcome”, etc)
That doesn’t look right either.
OR … do I need to have separate tables for each, so
Ack!!! There must be an efficient way of doing this and I’m too inexperienced with database design to see it. Mentoring would be most appreciated!