Best Method To Mark Records As Shared Between Two Companies 1

[Auguy]

SQL 2000. I have a .Net system running where I store records for two companies using the software in the same database and server. All billing and administrative work is performed by the main company. Almost all of the records are only accessible by the company that enters them. The Admin users can see all of the records. But there a few records that are shared between them and only the originating company can edit them. I use a SharedFlag Bit column for this. Right now my stored proc looks something like this

Select Column1,Column2, ... Where (Company = @Company Or SharedFlag) ...

There are no plans for more than two companies now, but you never know. Wondering what the best method is to handle this situation to eliminate the OR condition. Thought maybe another "Record Shared" table with the primary key of the master table and the company number and populate this table with both companies if the record is shared, or only one company if not shared. Then I could do an inner join to get all of the records each company is allowed to view. Any better methods or should I live with the OR?

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

Instead of a bit column for SharedFlag, you could have a "Permission" column (int).

In SQL Server, and int is 32 bits. The idea I have would support up to 32 companies.

Each bit in the Permission value would represent a company, so CompanyA would be bit 1, CompanyB would be bit 2 etc...

You could then use logical AND and OR to determine record visibility. If a record was only visible to CompanyA, then permission = 1. If a record is only visible to companyB, the value would be 2. If the record is visible to both, the value would be 3.

Example:

Declare @Temp Table(Data VarChar(20), Permission Int)

Insert Into @Temp Values('000', 0)
Insert Into @Temp Values('001', 1)
Insert Into @Temp Values('010', 2)
Insert Into @Temp Values('011', 3)
Insert Into @Temp Values('100', 4)
Insert Into @Temp Values('101', 5)
Insert Into @Temp Values('110', 6)
Insert Into @Temp Values('111', 7)

Declare @CompanyA int,
		@CompanyB Int,
		@CompanyC Int

Select @CompanyA = 1, @CompanyB = 2, @CompanyC = 4

-- Records for companyA, where the last bit is a 1
Select	*
From	@Temp 
Where	Permission & @CompanyA > 0

-- Record for company b, where the 2nd to last bit is a 1
Select	*
From	@Temp 
Where	Permission & @CompanyB > 0

-- Records for A or B, where the last bit is a 1 or the 2nd to last bit is a 1
Select	*
From	@Temp 
Where	Permission & (@CompanyA | @CompanyB) > 0

Take a look at the "Data" column. This is basically the binary equivalent of the "Permission" column.

Like I said earlier, this method would accommodate up to 32 companies. If you need more (up to 64 companies) you could use a big int instead.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

Thanks George, I was thinking about something like this I used over 30 years ago on a mainframe system. Back then we used the sum of the company #'s. We had Companies 1, 2, and 4. So 3 would be both company 1 & 2, 4 would be company 1 & 3, and 7 would be all three companies, etc. I like your way better and will try it out.

Auguy
Sylvania/Toledo Ohio

 

[Auguy]

George, must be too early in the morning. Your method is exactly what I used years ago, just a better way to use it. Memory is a terrible thing to waste, lose, err, ahhh, whatever. What was I saying?

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom