CODEWITHSUNDEEP
sql-serverrelational-database
cyberpirate92
cyberpirate92

Reputation: 3166

DB Design - Foreign key to multiple tables

I have the following tables

CREATE TABLE [dbo].[User]
(
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    [Username] NVARCHAR(200) NOT NULL,
    [Domain] NVARCHAR(200) NOT NULL,
    [IsAdministrator] BIT NOT NULL DEFAULT 'false',
    [IsGroup] BIT NULL DEFAULT 'false', 
)

CREATE TABLE [dbo].[Role]
(
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(500) NOT NULL, 
    [Description] NVARCHAR(1000) NOT NULL, 
    [ResourceType] NVARCHAR(100) NOT NULL,
)

A user can be assigned one or more roles for a particular resource

For example, Say there are 2 types of resources (Workspace and Process)

There will be a table for each resource type, and the schema for each resource will be different (hence I can't use a generic resource table)

CREATE TABLE [dbo].[Workspace]
(
    [WorkspaceId] UNIQUEIDENTIFIER NOT NULL, 
    [WorkspaceName] NVARCHAR(100) NOT NULL,
    [Description] NVARCHAR(500) NULL,

    CONSTRAINT [UC_WorkspaceName] UNIQUE([WorkspaceName]),
    CONSTRAINT [PK_Workspace] PRIMARY KEY([WorkspaceId])
)

CREATE TABLE [dbo].[Process]
(
    [ProcessId] UNIQUEIDENTIFIER NOT NULL , 
    [ProcessName] NVARCHAR(100) NOT NULL,

    CONSTRAINT [PK_BusinessProcessId] PRIMARY KEY ([ProcessId]), 
)

but irrespective of the resource type, the primary key will always be a UNIQUEIDENTIFIER.

Each user can have one or more roles assigned for a particular ResourceId and ResourceType

CREATE TABLE [dbo].[UserRole]
(
    [UserId] UNIQUEIDENTIFIER NOT NULL,
    [RoleId] UNIQUEIDENTIFIER NOT NULL,
    [ResourceType] NVARCHAR(100) NOT NULL,
    [ResourceId] UNIQUEIDENTIFIER NOT NULL,

    CONSTRAINT [PK_User_Role] PRIMARY KEY (ResourceId, UserId, RoleId, ResourceType),
    CONSTRAINT [FK_UserId] FOREIGN KEY ([UserId]) REFERENCES [User]([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [Role]([Id]) ON DELETE CASCADE
)

But with this design I can't set UserRole.ResourceId as a foreign key since it could either be WorkspaceId from the Workspace table or ProcessId from Process table.

This becomes a problem during resource deletion as I have to remove obsolete entries manually.

I know I could have 2 columns in UserRole called WorkspaceId and ProcessId, setting NULL based on resource type, but in my case the number of resource types will be quite large.

My question Is,

Is there any other way where I don't have to add a column for each new resource type or should I just go with the current design (without a foreign key)?

Upvotes: 0

Views: 191

Answers (1)

Peter
Peter

Reputation: 1065

I would combine workspace and process into a single table, and have them link to a ResourceType table.

CREATE TABLE [dbo].[User]
(
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    [Username] NVARCHAR(200) NOT NULL,
    [Domain] NVARCHAR(200) NOT NULL,
    [IsAdministrator] BIT NOT NULL DEFAULT 'false',
    [IsGroup] BIT NULL DEFAULT 'false', 
)

CREATE TABLE [dbo].[ResourceType]
(
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    [Description] NVARCHAR(100)
)

CREATE TABLE [dbo].[Role]
(
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(500) NOT NULL, 
    [Description] NVARCHAR(1000) NOT NULL, 
    [ResourceType_id] UNIQUEIDENTIFIER NOT NULL
)

CREATE TABLE [dbo].[Resource]
(
    [ResourceId] UNIQUEIDENTIFIER NOT NULL, 
    [ResourceName] NVARCHAR(100) NOT NULL,
    [Description] NVARCHAR(500) NULL,
    [ResourceType_id] UNIQUEIDENTIFIER NOT NULL,

    CONSTRAINT [UC_ResourceName] UNIQUE([ResourceName]),
    CONSTRAINT [PK_Resource] PRIMARY KEY([ResourceId])
)

CREATE TABLE [dbo].[UserRole]
(
    [UserId] UNIQUEIDENTIFIER NOT NULL,
    [RoleId] UNIQUEIDENTIFIER NOT NULL,
    [ResourceType] NVARCHAR(100) NOT NULL,
    [ResourceId] UNIQUEIDENTIFIER NOT NULL,

    CONSTRAINT [PK_User_Role] PRIMARY KEY (ResourceId, UserId, RoleId, ResourceType),
    CONSTRAINT [FK_UserId] FOREIGN KEY ([UserId]) REFERENCES [User]([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [Role]([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_ResourceId] FOREIGN KEY ([ResourceId]) REFERENCES [Resource]([ResourceId]) ON DELETE CASCADE
)

Upvotes: 0

Related Questions