Possible to use `one_to_many_through` associations in Sequel ORM?

Question

I have a case where one model is related 2 other ones. I am trying to correctly setup the model relationships between these 3 models.

A simplified example... The first 2 tables are clients and invoices:

db.create_table(:clients) do
    primary_key :id
    String :name
end

db.create_table(:invoices) do
    primary_key :id
    String :description
    Integer :balance
end

A third table, called files, contains records for files which can be related to either clients or invoices:

db.create_table(:files) do
    primary_key :id
    String :name
    String :path
    String :type # [image, pdf, word, excel]
end

There are 2 joiner tables to connect files to clients and invoices:

db.create_table(:clients_files) do
    Integer :client_id
    Integer :file_id
end

db.create_table(:files_invoices) do
    Integer :invoice_id
    Integer :file_id
end

The question is, how to correctly set up the relationships in the models, such that each client and invoice can have one or more related files?

I can accomplish this using many_to_many and has_many :through associations, however, this doesn't seem to be the right approach, because a given file can belong to only one customer or invoice, not to many.

I can also do this using polymorphism, but the documentation discourages this approach:

Sequel discourages the use of polymorphic associations, which is the reason they are not supported by default. All polymorphic associations can be made non-polymorphic by using additional tables and/or columns instead of having a column containing the associated class name as a string.

Polymorphic associations break referential integrity and are significantly more complex than non-polymorphic associations, so their use is not recommended unless you are stuck with an existing design that uses them.

The more correct association would be one_to_many_through or many_to_one_through, but I can't find the right way to do this. Is there a vanilla Sequel way to achieve this, or is there a model plugin that provides this functionality?

Answer 1

With your current schema, you just want to use a many_to_many association to files:

Client.many_to_many :files
Invoice.many_to_many :files

To make sure each file can only have a single client/invoice, you can make file_id the primary key of clients_files and files_invoices (a plain unique constraint/index would also work). Then you can use one_through_one:

File.one_through_one :client
File.one_through_one :invoice

Note that this still allows a File to be associated to both a client and an invoice. If you want to prevent that, you need to change your schema. You could move the client_id and invoice_id foreign keys to the files table (or use a single join table with both keys), and have a check constraint that checks that only one of them is set.

Note that the main reason to avoid polymorphic keys (in addition to complexity), is that it allows the database to enforce referential integrity. With your current join tables, you aren't creating foreign keys, just integer fields, so you aren't enforcing referential integrity.