Please consider the following SQL:
CREATE TABLE [dbo].[Permission]
(
[Id] UNIQUEIDENTIFIER NOT NULL,
[UserId] UNIQUEIDENTIFIER NOT NULL,
[ProductId] UNIQUEIDENTIFIER NOT NULL,
[OptionA] BIT NOT NULL DEFAULT 0,
[OptionB] BIT NOT NULL DEFAULT 0
)
CREATE TABLE [Product] (
[Id] UNIQUEIDENTIFIER NOT NULL,
[Name] NVARCHAR(256) NOT NULL
)
INSERT INTO [Product] ([Id], [Name])
VALUES ('0DB7F090-D314-438F-94F0-3C9E5D2AA353', 'Product 1')
INSERT INTO [Product] ([Id], [Name])
VALUES ('EB0B3A3C-D24E-4FAE-97FD-A944BED852A7', 'Product 2')
INSERT INTO [Product] ([Id], [Name])
VALUES ('76FFF884-BB9F-4EE6-B6A8-92A39C7C6FB6', 'Product 3')
INSERT INTO [Permission] ([Id], [UserId], [ProductId], [OptionA], [OptionB])
VALUES (NEWID(), '81C669A6-98FA-4BE1-98CF-5BE335819C2E' ,'0DB7F090-D314-438F-94F0-3C9E5D2AA353', 1, 1)
INSERT INTO [Permission] ([Id], [UserId], [ProductId], [OptionA], [OptionB])
VALUES (NEWID(), '81C669A6-98FA-4BE1-98CF-5BE335819C2E' ,'EB0B3A3C-D24E-4FAE-97FD-A944BED852A7', 1, 1)
INSERT INTO [Permission] ([Id], [UserId], [ProductId], [OptionA], [OptionB])
VALUES (NEWID(), '81C669A6-98FA-4BE1-98CF-5BE335819C2E' ,'76FFF884-BB9F-4EE6-B6A8-92A39C7C6FB6', 1, 1)
What I would like to do is select all the products a user has access to based on Option A. What I am wondering is this the best approach or is there a better one?
SELECT *
FROM [Product]
WHERE [Id] IN (
SELECT [ProductId]
FROM [Permission]
WHERE [UserId] = '81C669A6-98FA-4BE1-98CF-5BE335819C2E'
AND [OptionA] = 1
)
You may assume that there will be many more columns, in fact there might even be 50 columns of options. It will not grow infinitively but it will get a decent amount over time (total is unknown as the application will grow).
I am open to suggestions of a different design for permission table, however if you do please show me how you would select from product based on the permission with your new design.
