表示数据库中两个不同用户输入的数据的有效方法是什么?

Question

我有一个名为Request的表，数据将由两种类型的用户(公司员工和网站成员)输入。一开始我有一列叫做createdby。但是，人员表和成员表的主键都是带标识的整数。所以我必须改变它，因为可能有一个ID对应于两个实体。

然后我在请求表ByStaffId和ByMemberID中创建了两列。我不知道这是不是正确的方法。

Answer 1

给定现有表的约束，您的方法听起来合理，并且意味着您将能够创建从Request表到Staff和Member表的外键约束。使用之前的方法(单个CreatedBy字段)是不可能创建任何外键约束的。

如果重构现有设计的机会是可用的，请考虑将人员和成员视为相同抽象类型的子类(例如;"User")，使用一种模式将继承映射到这里描述的关系表。

现在为您设置的方式通过创建两列所做的就是解决方案。

但是，它不是一个好的。基本上，你应该只有一个Users表，以某种方式来区分用户类型，如一个单独的表(角色，UserType等)。然后，您将能够在Request表中拥有一个外键，该外键将引用创建初始请求的用户。

下面是如何使用一个与StaffUser表和MemberUser表都具有1对1关系的公共User表将Staff和Members关联在一起的示例。

当然，当选择/插入/更新/删除用户时，这种方法会导致更大的复杂性，所以您可以决定是否值得这样做。

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, '[e-mail]')
insert into Request values (@UserID)
insert into [User] values ('member2')
set @UserID = scope_identity()
insert into MemberUser values (@UserID, '[e-mail]')
insert into Request values (@UserID)
insert into [User] values ('member3')
set @UserID = scope_identity()
insert into MemberUser values (@UserID, '[e-mail]')
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]

---

下面是一个稍微复杂一点的结构，它实现了与上面的示例相同的功能，但是在这种情况下，Member和Staff与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', '[e-mail]', @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', '[e-mail]', @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', '[e-mail]', @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]

为什么要使用单独的表来区分成员和人员?我宁愿实现角色表，并通过分配的角色来区分用户。

。创建的列可能引用这些表之一?这是一个坏主意，但如果您使用Guid作为staff和members

中的主键，它可能会起作用。