How to deal with tenant referencing public records?

[cdekker]

Imagine the following setup:

• Postgres with use_schemas: true
• Having an excluded model Category in the public schema
• Having a model Product in the tenant schema
• The Product belongs_to the Category

To summarize: each tenant has their own set of Products, but they all share the same pool of Categories.

Now by default, Rails creates migrations with index and foreign_key: true. This breaks, for obvious reasons, when trying to create a Product in a tenant schema with a category_id, since that ID is in a different (public) schema.

Is it enough to simply not create a foreign_key on these columns referencing the public schema? Or are there other implications when referencing between models in the tenant and public schemas? What are the best practices when dealing with this?

My real use-case is a bit more complex where I need to reference thousands of records in the public schema across multiple tables.

[mikecmpbll]

there are numerous issues discussing this already. it's possible to create the foreign key reference across schemas, as I understand it, but it's not supported in Rails schema.rb format.

https://github.com/influitive/apartment/issues?q=is%3Aissue+foreign+key+is%3Aclosed

[mikecmpbll]

397 #382 #248 #227