We have 2 databases. One of them stores a table of shops, the other one - a table of products. Using Data Model in the administration panel I want to select the Shop ID field as a foreign key in the table of products. But I see nor this field neither other fields of shops’ table.
I also don’t see them in the simple and the custom question.
Is there any solution for this case or we have to change the architecture of our database to store all the tables in one and only database?
Thanks!
Ideally, store in one database. Failing that, I normally just use SQL Server with linked databases.
Thanks for your answer! So, am I right that if we link databases in SQL Server, we will be able to link tables in Metabase?
In SQL, you create two linked datasource to link to your other 2 databases. Then create a blank database containing views referencing the links.
It’s a bit of a faff, but works really well and is surprisingly fast (I’m not sure how much magic SQL does in the background).
I’ve a customer who’s supposed to be paying me to test this on their Crystal Reports. If they give me the go ahead, I’ll probably write a blog on the methodology.
Thanks! We will try this way)
Hi Andrew - did you happen to ever write this up? I’m curious whether it would help in my case.
Not yet. A customer was due to pay for me to do this on their server for Crystal Reports (massive performance improvement). When I’m a bit quieter, I’ll write it up.