Centralized database for multiple applications

I think I have a fairly straight forward design question.

Say I have 3 applications

App1
App2
App3

They all need to access common data, so I made a common database with a table called Locations

All three apps can update the information in the table, but I want an IsEnabled for each application so each application can have the 开发者_运维技巧functionality to enable/disable each Location row in the table.

I don't think I want to make a bit column in the table for every app to see if its enabled or not but I don't know? Thanks!

You could have a separate table as follows:

CREATE TABLE ApplicationLocations
(
    ApplicationID INT,
    LocationID INT,
    IsEnabled BIT
);

Now instead of adding a column to the locations table for each application, you just add a row to this mapping table. You can also extend this to support other properties that may be specific to an application:location combination.

If it was me, I would create an abstraction layer between the database and these applications. This would eliminate having to refactor all of the applications each time a database change is made and also give you a variety of options for controlling access to the data.

You could do this quite easily:

• create three separate AppXEnabled BIT columns - let each app set their own "enabled" flag
• create a "combined" computed column that makes sure at least one of the three flags is set

Something like this:

CREATE TABLE dbo.YourTable
   (ID INT IDENTITY PRIMARY KEY, 
    ...(your columns here) .....,
    App1Enabled BIT, 
    App2Enabled BIT, 
    App3Enabled BIT)

ALTER TABLE dbo.YourTable
ADD AtLeastOneEnabled AS App1Enabled | App2Enabled | App3Enabled PERSISTED

Now, your rows will have the three separate AppXEnabled flags - and a combined flag AtLeastOneEnabled that will the true (1) when at least one of the flags is set, and will be false (0) when none of the three app flags is set.

This is a computed column, meaning it will always be up to date and updated by SQL Server as needed.