what is efficient way to represent data entered by two different users in the database

Question

I have a table called Request and data will be entered by two types of users (Company staff and site members). Initially I had a column called createdby. But, the staff and the members table primary keys are integers with identity. So i had to change it because there could be an ID that corresponds to both entities.

Then I have created two columns in the the request table ByStaffId and ByMemberID. I wonder whether it is the right way.

Answer 1

Below is an example of how to relate the Staff and Members together using a common User table that has a 1-to-1 relationship with both a StaffUser table and a MemberUser table.

Granted, this approach results in greater complexity when selecting/inserting/updating/deleting users, so you can decide whether this is worth the extra complexity.

create table [User]
(
    UserID int identity(1,1) not null primary key,
    Username nvarchar(50) not null
)

create table StaffUser
(
    UserID int not null primary key references [User] (UserID),
    FirstName nvarchar(50) not null,
    LastName nvarchar(50) not null
)

create table MemberUser
(
    UserID int not null primary key references [User] (UserID),
    Email nvarchar(100) not null,
)

create table Request
(
    ByUserID int not null references [User] (UserID),
)

declare @UserID int

insert into [User] values ('john.smith')
set @UserID = scope_identity()
insert into StaffUser values (@UserID, 'John', 'Smith')

insert into Request values (@UserID)

insert into [User] values ('billy.bob')
set @UserID = scope_identity()
insert into StaffUser values (@UserID, 'Billy', 'Bob')

insert into Request values (@UserID)

insert into [User] values ('member1')
set @UserID = scope_identity()
insert into MemberUser values (@UserID, 'member1@awesome.com')

insert into Request values (@UserID)

insert into [User] values ('member2')
set @UserID = scope_identity()
insert into MemberUser values (@UserID, 'member2@awesome.com')

insert into Request values (@UserID)

insert into [User] values ('member3')
set @UserID = scope_identity()
insert into MemberUser values (@UserID, 'member3@awesome.com')

insert into Request values (@UserID)

-- select staff
select
    StaffUser.UserID,
    [User].Username,
    StaffUser.FirstName,
    StaffUser.LastName
from StaffUser
inner join [User] on
    [User].UserID = StaffUser.UserID

-- select members
select
    MemberUser.UserID,
    [User].Username,
    MemberUser.Email
from MemberUser
inner join [User] on
    [User].UserID = MemberUser.UserID


-- select all users
select
    StaffUser.UserID,
    [User].Username
from StaffUser
inner join [User] on
    [User].UserID = StaffUser.UserID

union all

select
    MemberUser.UserID,
    [User].Username
from MemberUser
inner join [User] on
    [User].UserID = MemberUser.UserID


select * from Request


drop table Request
drop table MemberUser
drop table StaffUser
drop table [User]

---

Below is a slightly more complicated structure that accomplishes the same thing as the example above, but in this case Member and Staff are more decoupled from User.

create table [User]
(
    UserID int identity(1,1) not null primary key,
    CreatedOn datetime not null default getdate()
)

create table StaffUser
(
    UserID int not null primary key references [User] (UserID)
)

create table MemberUser
(
    UserID int not null primary key references [User] (UserID)
)

create table Staff
(
    StaffID int identity(1,1) not null primary key,
    FirstName nvarchar(50) not null,
    LastName nvarchar(50) not null,
    UserID int null references StaffUser (UserID),
)

create table Member
(
    MemberID int identity(1,1) not null primary key,
    Username nvarchar(50),
    Email nvarchar(100) not null,
    UserID int null references MemberUser (UserID),
)

create table Request
(
    ByUserID int not null references [User] (UserID),
)

declare @UserID int

insert into [User] default values
set @UserID = scope_identity()
insert into StaffUser values (@UserID)
insert into Staff values ('John', 'Smith', @UserID)

insert into Request values (@UserID)

insert into [User] default values
set @UserID = scope_identity()
insert into StaffUser values (@UserID)
insert into Staff values('Billy', 'Bob', @UserID)

insert into Request values (@UserID)

insert into [User] default values
set @UserID = scope_identity()
insert into MemberUser values (@UserID)
insert into Member values ('member1', 'member1@awesome.com', @UserID)

insert into Request values (@UserID)

insert into [User] default values
set @UserID = scope_identity()
insert into MemberUser values (@UserID)
insert into Member values ('member2', 'member2@awesome.com', @UserID)

insert into Request values (@UserID)

insert into [User] default values
set @UserID = scope_identity()
insert into MemberUser values (@UserID)
insert into Member values ('member3', 'member3@awesome.com', @UserID)

insert into Request values (@UserID)

-- select staff
select
    Staff.StaffID,
    Staff.FirstName,
    Staff.LastName,
    Staff.UserID
from Staff

-- select members
select
    Member.MemberID,
    Member.Username,
    Member.Email,
    Member.UserID
from Member

-- select all users
select
    [User].UserID,
    Staff.FirstName + ' ' + Staff.LastName as Name,
    [User].CreatedOn
from [User]
inner join Staff on
    Staff.UserID = [User].UserID

union all

select
    [User].UserID,
    Member.Username as Name,
    [User].CreatedOn
from [User]
inner join Member on
    Member.UserID = [User].UserID


select * from Request


drop table Request
drop table Member
drop table Staff
drop table MemberUser
drop table StaffUser
drop table [User]

Answer 2

The way thing are set up for you now what you did by creating the two columns is the solution.

But, it's not a good one. Basically you should only have one Users table with some way to differ the user types like a separate table(Roles, UserType etc). Then you would be able to have a foreign key in your Request table that will reference the user that created the initial request.

Answer 3

Why you ever wanted to use separate tables to distinguish members from staff? I'd rather implement Role table and distinguish users by assigned roles.

.createdby column that may reference to one of these tables? Bad idea but it might work if you'd use Guid as primary key in staff and memvers

Answer 4

Given the constraints of the existing tables, your approach sounds reasonable, and means that you will be able to create foreign key constraints from the Request table to the Staff and Member tables. It would not have been possible to create any foreign key constraints with your previous approach (a single CreatedBy field).

If the opportunity to refactor the existing design is available, consider treating Staff and Members as subclasses of the same abstract type (e.g. "User"), using one of the patterns for mapping inheritance to relational tables described here.