Need Database design mentoring

Phoenix Forum Questions / Help
,
1

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 **:

Template Table
id
template

Group Table [References just 3 templates]
id
welcome_member_template_id (references a Template id)
inactive_warning_template_id
delete_member_template_id

Event Table [References just two templates]
id
reminder_template_id (references a Template id)
some_other_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
id
template_type (“group” or “event”)
group_id
event_id
message_type (“reminder”, “welcome”, etc)
template

That doesn’t look right either.

OR … do I need to have separate tables for each, so
Group_Templates
Event_Templates

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!

1 Like
2

A belongs_to association tells Ecto there is a foreign key on the schema’s table: in this case, Ecto would look for a column named group_id on the templates table.

That doesn’t match the tables you described, so I expect that it would either fail to compile or fail at runtime.

Correspondingly, a has_many association tells Ecto to look for a foreign key column on the other table, so this would also look for a group_id on templates and fail.

You want associations like belongs_to :welcome_msg, Template in Group to work with the columns described. belongs_to will internally call field, so you’ll need to delete those lines.

The reverse association from Template is harder to write - you could write them all out:

# in Template schema
has_many :welcome_groups, Group, foreign_key: :welcome_msg_id
has_many :inactive_groups, Group, foreign_key: :inactive_msg_id

but I’m not sure if those are useful. You’ll need to think about if / how your application needs to start from a template and find a group or event.

1 Like
3

Thank you! You confirmed what I suspected.

What is the harm in not creating an association between the tables? Let’s say I just have a column that contains IDs that point to Templates. But I create no reference in the migration or schema. What is the risk of doing this? Once I retrieve that ID from Groups … I can just go search the template by ID in the Template table. So do IDs always need reference associations specified in the migration and schema?

4

In general it is considered good practice to ensure data consistency at database level instead of only in the application level.

What would happen if you tried to add a template for a group that doesn’t exist? Can you be sure that the application code will never do that?

And if you delete a group and want to also delete all the templates from this group, why do it in the application code if the database can handle that for you?

From the postgres documentation on foreign key constraints:

We know that the foreign keys disallow creation of orders that do not relate to any products. But what if a product is removed after an order is created that references it? SQL allows you to handle that as well. Intuitively, we have a few options:

Disallow deleting a referenced product

Delete the orders as well

Something else?

2 Likes
5

Adding this for anyone else who runs into this same issue. The term for this database issue is Polymorphic Associations. You can read more about solutions for this at this link.

You need to scroll down to the section on “belongs_to” and you’ll see a discussion about how to handle polymorphic associations. There are three approaches. In the end,I decided to use the third approach which basically creates a third join table. So the Template table will just have an ID and template and no references to Groups or Events. Then I’ll have two other tables: join_group_templates and join_event_templates. This will allow the app to take advantage of the Ecto delete features that @thomas.fortes referenced above.

1 Like