Where Do You Put Commonly Shared Tables, Stored Procedures, and Functions?

Question

I have a SQL Server with a number of databases. Most are for applications, but some store data for reporting and analysis. I also have information that is not specific to any one database, but can be used by several of them.

A good example is my company's fiscal calendar. I store this information in a table. Putting the same fiscal calendar table in each database is a bad idea for me. Even with the negative of having multiple database dependencies, I think it is worth it because otherwise there is too much risk for inconsistency. What I do now is put the fiscal calendar and other similar functions and procedures in a database simply titled "Community".

I have the rare and glorious opportunity of moving to a new server and refactoring everything as I go. I am wondering if I should change this practice. Below are a few specific questions:

• Am I unaware of any disadvantages of my current method?
• Is there a better place or name to use to store this type of information?
• What is your experience with issues like this, and am I missing what should be an obvious solution?

Thanks

Answer 1

You've already taken the important step of separating the shared data into its own database. I don't think there's a better approach. The name is fairly subjective, but Common is another term frequently used for this purpose.

Answer 2

I would hide this behind a "shared data service" or something. Not rely on the existence of a database.

You don't have to be a big shop before you need to put one app onto it's own servers then you're bollixed.

At the very least, I'd use a linked server to hide it even if on the same server so you are independent of actual server names.