I believe I've got a fairly easy design question.

Say I've 3 programs

App1
App2
App3

All of them have to access common data, and so i designed a common database having a table known as Locations

The 3 applications can update the data within the table, however i want an IsEnabled for every application so each application might have the functionality make it possible forOreliminate each Location row within the table.

I do not think I wish to create a bit column within the table for each application to ascertain if its enabled or otherwise but I'm not sure? Thanks!

You might have another table the following:

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

Now rather than adding a column towards the locations table for every application, you simply give a row for this mapping table. You may also extend this to aid other qualities that might be specific for an application:location combination.

Whether it was me, I'd create an abstraction layer between your database which programs. This could eliminate needing to refactor all the programs every time a database change is created as well as provide you with a number of choices for controlling accessibility data.

You could do this this very easily:

  • create three separate AppXEnabled BIT posts - let each application set their very own "enabled" flag
  • produce a "combined" calculated column which makes sure a minumum of one from the three flags is placed

Something similar to 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 may have the 3 separate AppXEnabled flags - along with a combined flag AtLeastOneEnabled which will the true (1) when a minumum of one from the flags is placed, and will also be false (0) when no three application flags is placed.

This can be a calculated column, meaning most commonly it is current and up-to-date by SQL Server when needed.