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
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 BITposts - 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.