CODEWITHSUNDEEP
sql-serverasp.net-mvcdesign-patterns
Jose3d
Jose3d

Reputation: 9277

Multiple websites that uses same database structure

I have three websites which uses an abstract database structure with tables like: Items, Places, Categories, etc... and stored procedures like GetItemsByCategory, GetRelatedItems, etc... Actually im using exactly the same database structure for these 3 different websites.

From a code perspective im using the same code for all websites (except the HTML which is specific foreach one), and all the common code is in few projects used by all websites, so everytime that i detect a bug (which is in all websites) i just fix it on one place (the common part used by all) and automatically all websites get the fix.

Actually im using Asp.net MVC3 and Sql server.

Everytime i want to extend some funcionality, and i need a new table, stored procedure or something related with database, i have to do the modification in each database.

Upvotes: 1

Views: 526

Answers (2)

anon
anon

Reputation:

If the databases are on a single server, you could generate the script for the procedure from Management Studio, and make sure to use the option to "check for object existence" (Tools > Options > SQL Server Object Explorer > Scripting). This will yield something like this (most importantly it produces your stored procedure code as something you can execute using dynamic SQL):

USE DBName;
GO

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

IF NOT EXISTS (...)
BEGIN
    EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.whatever ...
    '
END
GO

Now that you have this script, you can modify it to work across multiple databases - you just need to swipe the @statement = portion and re-use it. First you need to stuff the databases where you want this to work into a @table variable (or you can put this in a permanent table, if you want). Then you can build a command to execute in each database, e.g.

DECLARE @dbs TABLE (name SYSNAME);

INSERT @dbs(name) SELECT N'db1';
INSERT @dbs(name) SELECT N'db2';
INSERT @dbs(name) SELECT N'db3';

-- now here is where we re-use the create / alter procedure command from above:

DECLARE @statement NVARCHAR(MAX) = N'CREATE PROCEDURE dbo.whatever ...
    ';

-- now let's build some dynamic SQL and run it!

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + '
  EXEC ' + QUOTENAME(name) + '.dbo.sp_executesql N''' + @statement + ''';'
  FROM @dbs;

EXEC sys.sp_executesql @sql;

Alternatively, you could create a custom version of my sp_msforeachdb or sp_ineachdb replacements:

I used to use a tool called SQLFarms Combine for this, but the tool doesn't seem to exist anymore, or perhaps it has been swallowed up / re-branded by another company. Red Gate has since produced SQL Multi Script that has similar functionality.

Upvotes: 1

macou
macou

Reputation: 787

If you added a column to all your tables called websiteId you could just have one database. Store the unique websiteId in each site's web.config and just pass it with each request for data. Obviously each site's data is stored with their websiteId so data can be queried per website.

It means a bit of refactoring in your db and any calls to your your db, but once done, you only have one database to maintain.

Of course this is assuming your databases are on the same server...

Upvotes: 0

Related Questions