Polymorphic in DBML?

Ask the Community
,
1

I dont know how to design a polymorphic table in dbdiagram (DBML). Am I miss something or polymorphic still not there yet?

2

Hi Skyler,

Polymorphism is a central concept in OOP, and there are many ways to represent it when mapping then into database tables. Can you provide an example of your design and what you want to achieve through polymorphism?

3

I dont think this so far earlier. Example can simple as “a comment” can belong to “users”/“admin”. Thank for your help!

5

Hi Skyler,

Here is one way to model your example in dbml

table user {
  id int [pk]
}

table admin {
  id int [pk]
}

table comment {
  id int [pk]
  commenter_id int
  commenter_type text
  comment_text text
}

Another way is

table user {
  id int [pk]
}

table admin {
  id int [pk]
}

table comment {
  id int [pk]
  admin_id int
  user_id int 
  comment_text text
}


Ref: "admin"."id" < "comment"."admin_id"

Ref: "user"."id" < "comment"."user_id"

I hope this will help.

1 Like
6

I’m experimenting with dbml and I’m also keen to model a polymorphic association.
The example provided by Phuc_Nguyen is the schema style that I’m keen on, but I’d like the diagram to reflect the possible targert for the commenter_type and commenter_id. Is there a way to create references from those columns in the comment table to the intended commenter_type tables?

7

Hi @ivar,

In dbml, there’s nothing prevents you from referencing a column to multiple colums. For example, the below will still render a diagram. So if you only interested in the diagram, then all you need to do is this:

table user {
  id int [pk]
}

table admin {
  id int [pk]
}

table comment {
  id int [pk]
  commenter_id int
  comment_text text
}

Ref: "user"."id" < "comment"."commenter_id"

Ref: "admin"."id" < "comment"."commenter_id"

However, when exporting those to SQL, you will find that there’s no DB allowing such things. This means that in SQL, a column can only reference to at most 1 other column. Therefore, to achieve what you want while still keeping it compatible with SQL, we can create a supertype that can be used to represent both user and admin. For example:

table user {
  id int [pk, ref: > account.id]
}

table admin {
  id int [pk, ref: > account.id]
}

enum account_type {
  user
  admin
}

table account {
 id int [pk]
 type account_type // This field is not strictly necessary, but if you want to distinguish whether a commenter is an admin or a user, this field will help you find it faster.
}

table comment {
  id int [pk]
  commenter_id int
  comment_text text
}

Ref: "account"."id" < "comment"."commenter_id"
1 Like
8

Thank you for this excellent followup!