It is possible to share non-tenancy tables with tenants

[robmachado]

There are many tables that have the same content for whoever the tenant is, so instead of replicating them every time, it would be interesting to be able to share this tables with all tenants.

An example is the table of the cities of the country, among many others.
These tables simplify the entries and avoid typing errors.

[stancl]

Not supported yet, but I'm thinking about it. See #57.

I've done some research about this and it's a bit complicated.

So say you have a list of cities in a cities table in some shared database. You could define a getConnectionName() method on the Cities model to use some non-tenant connection and it would work perfectly. But only if you don't need relationships.

If you have a city column on the users table, referencing a record in the cities table, you can't use foreign keys. That sucks a bit, as it passes a lot of the DB's responsibility to the application (ensuring that a referenced record exists, ON DELETE, ON UPDATE).

You could use cross-database queries, e.g. SELECT * from tenant123.users WHERE tenant123.users.id = 1 JOIN cities_db.cities ON tenant123.users.city_id = cities_db.cities.id, but Laravel is not built for that use case, so it's also not trivial.

The DB facade will let you execute cross-database queries, but it's up to you to write the query correctly for your database driver. For example, sqlite supports cross-database queries, but you first need to attach the databases. Not sure how that would work in Laravel.

When it comes to Eloquent, I'm not sure if using two related models with different DB connections specified in getConnectionName() would work. If I recall correctly, I've seen conflicting reports on this. I haven't tested this myself yet.

Even if that did work, you would still need to handle the record exists/ON UPDATE/ON DELETE logic in your app.

I want to add this at some point, but I haven't yet figured out how I'd implement this.

[robmachado]

Thank you, you are right in your postings. I will study a little more what would be the actual use cases and the possible solution for those cases. However, we will keep these tables being duplicated.

[stancl]

I will close this for now, but if you find something that could be used as a general solution provided by this package, please let me know.

Thank you.