Implement polymorphic relationships

[stevefan1999-personal]

I wish we could have polymorphic relationships like we could do in Hibernate.

Indeed I know you can use interface to abstract out some of the fields, and put them into one own entity with such shared traits, to mock the behavior of polymorphic relationships, but it simply won't synchronize with the database design in mind.

[ashokgelal]

I've not used Hibernate but I've used Laravel Eloquent, which supports Polymorphic relationships as well.

I take a different approach when it comes to polymorphic relationship, a technique that I actually learned while using Elixir Ecto—using multiple nullable foreign keys. In practice I've found this approach to work better than other ways and hence this is now my preferred way. This is also self documenting and is within the boundary of a relational database. Here is an example from one of my projects: https://github.com/alpas/fireplace/blob/master/src/main/kotlin/entities/Activity.kt

In the above file - an activity could be a task activity or a project activity.

[vincentlauvlwj]

@stevefan1999-personal I don't quite understand what you meant by polymorphic relationship. Can you give me an example of how you do it in Hibernate?

[stevefan1999-personal]

@vincentlauvlwj Lemme use an example instead of some academic bullshit: imagine a classical situation where you have Person entity, Student entity extends from Person, so does Teacher entity. I mean you wouldn't like them to implement the same column twice and instead want to put them into a master table and then use a SQL view to adjoin the subtable and master table right?

[lyndsysimon]

Here's a contrived example of how I would expect this to work, given my experience with Python/SQLAlchemy and other ORMs:

Consider the following tables:

create type employee_type as enum ('engineer', 'manager');

create table employee (
  id integer primary key,
  typ employee_type not null default 'manager'
  name varchar(128) not null
);

create table manager (
  id integer primary key references employee(id),
  annual_bonus integer
);

create table engineer (
  id integer primary key references employee(id),
  manager_id integer references manager(id)
);

insert into employee (id, name, typ) values
  (1, 'PHB', 'manager'),
  (2, 'Dilbert', 'engineer')
;
insert into manager (id, annual_bonus) values (1, 25000000);
insert into engineer (id, manager_id) values (2, 1);

The idea here is that column that are shared for all employees are on the employee table. The field employee.typ defines the type of employee (and therefore which additional table should be joined to get the information specific to that type).

How I would expect this to work in Ktorm:

println(database.employees.count())
//2
println(database.managers.count())
// 1
println(database.engineers.count())
// 1

for (employee in database.employees.asKotlinSequence()) {
  println(employee::class)
}
// app.model.Manager
// app.model.Engineer

Querying the Employees table should return a heterogenous iterable of instances of the appropriate dependent tables, while querying the dependent tables should return instances only of the employees of that type.

In terms of implementation, all three tables objects should build their query off the employee table. Employees should include an outer join to all dependent tables (manager and engineer in this case), then instantiate the correct entity according to the value of typ. Engineers should query employee, filter on employee.typ = 'engineer', and left outer join to engineer.

I hope this makes things a bit more clear, and that my examples aren't broken too badly to understand :)

Ref: SQLAlchemy's documentation on "single table inheritance" should be help show this without requiring you to understand that entire library.

[lyndsysimon]

@stevefan1999-personal - is the above what you were thinking?

[stevefan1999-personal]

@lyndsysimon Exactly. That's much more intuitive in OOP nature to design this way