How to design my database

Transact-SQL
1

Hello everyone,

Going to build a new application but not sure how to design the database.

Database 1:
Our master databas, where we store all business logic and create stored procedures, views eg. And from them we want to call other databases based on input to sp.

Users login to a windows application and choose database to work against, and that databasename will be on of the params till all sp call in our master database.

Database 2, 3, 4, 5
They all have the same structure within, just different data (companies).

From the application we only going to call sp stored in our master (Database 1).
An example of how a stored procedure can look like

Sample Call:
EXEC dbo.GetAllProducts 'Database2'

GetAllProducts
Param DatabaseName VARCHAR(255);
BEGIN
-- Not want to hardcode database here, use our parameter.
SELECT * FROM [Database1].[dbo].[Products]
END

Solution 1:
Maybe I can use different schemas for my stored procedures in my masterdatabase.
SP: [database2].[GetAllProducts] (And within that I just hardcode the database.
SP: [database3].[GetAllProducts] (And within that I just hardcode the database.
Problem: I want to have same business logic within all SP and dont want to dublicate every singel object.
From the application I can add correct schema based on their picked database.

Solution 2:
Use Dynamic SQL (don't want that)

Solution 3:
Place all database object within every database and skip our master database.
Not what I want, want to have a Clean DB with all the logic. And I prefer not touching our target databases at all.

2

With your given options I would choose Solution 3 for the best security. You should be able to script updates to all databases.

I would also look at just having one database with a company code in each table. Row-Level security could then be used to control who can see what. This is not totally secure but is probably good enough in a lot of cases.

3

Thank you for your answer.
I cannot change the current structure of the databases 2,3,4, just add and maintain new objects.

The solution that I think fit us best right now are option 1.

4

Then why ask for advice? What security safeguards etc do you have? What about recompiles etc?

1 Like
5

If you run everything through stored procs, you could put all code in SQL's "master" db. Then specify the db as part of the EXEC.

You would have to start each proc name with "sp_" and mark the procs as system objects.

But you could run them like this:
EXEC database2.dbo.sp_GetAllProducts
EXEC database3.dbo.sp_GetAllProducts
etc.

6

I really just mean that of the three options, option 1 is the one that suits us best. Having said that, I am not think any of the alternative is good enough.

7

Thanks for a new option, but don't want to store it in the master database.

9

Any more takers ; )

1 Like
10

You need to rethink the approach - instead of looking at driving everything from the 'master' database to the individual databases, you need to run everything from the individual databases.

That can be done using SYNONYMS in each individual database that reference the objects in the 'master' database as needed.

1 Like
11

Thanks for this information.

12

Not sure I understand,

MyMasterDatabase:
I can create a SYNONYMS to an object: DB1.dbo.Table1 and call i Table1 and then perform select like the following:
SELECT * FROM Table1

But how to fetch data from database 2 then, with the same query?

13

No - you create the synonyms in the target/individual databases referencing the master database.

And yes, the code will be duplicated in each database but that can be managed. To update the code across all target databases - a simple multi-server query works. Better yet, a deployment process that ensures deployment across all target databases.

Your master database then maintains objects and data that are shared. You could even create those objects with a column specifying the source - so a single table in master could have separate data in each table.

14

Maybe I expressed myself in a bad way; it’s hard to explain with words. All individual databases have database objects that I want to call, and all the data is stored in these databases. I want to call stock stored procedures in the individual database, e.g., CreateCustomer, and the customer will be created in that database also.

And from my master database, I want to be able to call different databases based on some input to a stored procedure.

15

I understood your request - you want to centralize your code in a single database instead of deploying the code to each individual database. But without using dynamic SQL or creating the objects in the master database and setting them up as system objects - that isn't going to work.

So your option 1 because unwieldy because you have to duplicate the code for each database. If you have to make a change, you have to make the change to each individual procedure/view/function for each schema.

You don't want your solution 2 - which is dynamic SQL.

You don't want to put the objects in master and mark them as system objects.

And you don't want to put any code in your target databases - so that leaves you with an unwieldy method that is going to be hard to manage and maintain.

1 Like
16

The only option I don't really understand is this, how that will work:
You don't want to put the objects in master and mark them as system objects.

Do they have to belong to the real master database to mark them as system objects?

17

Yes, objects must be in the master db to designate them as system objects.

The advantage it gives is that the code runs in the context of the current db, whatever that happens to be.

For example, if you create proc "sp_my_sys_proc" and mark it as a system object, then if you run:

USE db1;
EXEC dbo.sp_my_sys_proc

it will run in db1 (that is, the default location for any tables will be db1).

If you specify:
EXEC db2.dbo.sp_my_sys_proc

it will exec in db2.

18

I mark that one as the solution, but guess that we have to rethink the whole concept instead.
But I have learned something new :wink:

Thank you!