1

I'm am currently developing one project of many to come which will be using its own database and also data from a central database.

Example: the database "accountancy" with all accountancy package specific tables. the database "personelladministration" with its specific tables

But we also use data which is general and will be used in all projects like "countries", "cities", ... So we have put these tables in a separate database called "general"

We come from a db2 environment where we could create foreign keys between databases. However, we are switching to MS SQL server where it is not possible to put foreign keys between databases.

I have seen that a workaround would be to use triggers, but I'm not convinced that is a clean solution.

Are we doing something wrong in our setup? Because it seems right to me to put tables with general data in a separate database instead of having a table "countries" in every database, that seams difficult to maintain and inefficiënt.

What could be a good approach to overcome this?

randomizer
  • 1,619
  • 3
  • 15
  • 31
  • Read the answers [here](http://stackoverflow.com/questions/4452132/add-foreign-key-relationship-between-two-databases). Probably they'll solve your doubt. – dario Jan 14 '15 at 14:53
  • I did but I cannot find a specific answer to my question how to organize the data. – randomizer Jan 14 '15 at 14:56

2 Answers2

1

I would say that countries is not a terrible table to reproduce in multiple databases. I would rather duplicate static data like that than use more elaborate techniques. There is one physical schema per database in sql server and the schema can not be shared. That is why people use replication or triggers for shared data.

I can across this problem a while back. We have one database for authentication, however, those users have to be shared across multiple applications some of which have their own database.

Here is my question on this topic.

We resorted to replication and using an custom Authentication/Registration service agent to keep the data up to data.

Using views, in what Sourav_Agasti suggested in his answer, would be the most straight forward approach for static data. You can create views and indexed views and join data from databases on linked servers.

Community
  • 1
  • 1
Ross Bush
  • 14,648
  • 2
  • 32
  • 55
0

Create a loopback linked server and then create a view(if required, on each database) which accesses the table in this "central database" through this linked server. There will be a minor performance impact but it more than enough compensates by being very simiplistic.

SouravA
  • 5,147
  • 2
  • 24
  • 49
  • I don't get why this would be better? Because I still can't create a foreign key. Therefore I can just create a query with a join between two databases, why should I need a view? – randomizer Jan 14 '15 at 15:37
  • I didn't get your requirement earlier. In that case, I will edit my answer. – SouravA Jan 14 '15 at 15:39