实现别名表(自引用多对多)

krerum 发布于 2019-03-09 sql 最后更新 2019-03-09 14:34 3 浏览

我正在尝试模拟别名关系。也就是说,我的人员表格中的多个记录可能代表相同的实际人员。我不在乎“主”人是谁。所有人员记录将具有相同的权重。 过去我已经用下面的两张表实现了这一点。

-------------    ------------
| Person    |    | Alias    |
|-----------|    |----------|
| PersonID  |    | AliasID  |
| LastName  |    | PersonID |
| FirstName |    ------------
-------------
以下是一些示例数据:
Person (1, 'Joseph', 'Smith')
Person (2, 'Jane', 'Doe')
Person (3, 'Joe', 'Smith')
Person (4, 'Joey', 'Smith')
Alias(1, 1)
Alias(1, 3)
Alias(1, 4)
我想我可以将AliasID移动到Person表中,因为PersonID字段之间存在一对一的关系。但是,我可能希望在未来的某个时间点将其他字段添加到别名表(如序列号等)。 有没有更好的方法来模拟这个比我在这里?
已邀请:

vvelit

赞同来自:

我就是这样做的。

--DROP TABLE [dbo].[Alias]
GO
--DROP TABLE [dbo].[RealPerson]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[RealPerson]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
        DROP TABLE [dbo].[RealPerson]
    END
GO
CREATE TABLE [dbo].[RealPerson]
(
    RealPersonUUID          [UNIQUEIDENTIFIER] NOT NULL DEFAULT NEWSEQUENTIALID()
    , CreateDate                smalldatetime default CURRENT_TIMESTAMP
    , MyCompanyFriendlyUniqueIdentifier             varchar(128) not null
)
GO
ALTER TABLE dbo.RealPerson ADD CONSTRAINT PK_RealPerson
PRIMARY KEY NONCLUSTERED (RealPersonUUID)
GO
ALTER TABLE [dbo].[RealPerson]
    ADD CONSTRAINT CK_MyCompanyFriendlyUniqueIdentifier_Unique UNIQUE (MyCompanyFriendlyUniqueIdentifier)
GO
GRANT SELECT , INSERT, UPDATE, DELETE ON [dbo].[RealPerson] TO public
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Alias]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
        DROP TABLE [dbo].[Alias]
    END
GO
CREATE TABLE [dbo].[Alias]
(
      AliasUUID                         [UNIQUEIDENTIFIER] NOT NULL DEFAULT NEWSEQUENTIALID()
    , RealPersonUUID                    [UNIQUEIDENTIFIER] NOT NULL
    , CreateDate                        smalldatetime default CURRENT_TIMESTAMP
    , LastName                          varchar(128) not null
    , FirstName                         varchar(128) not null
    , PriorityRank                      smallint not null
)
GO
ALTER TABLE dbo.Alias ADD CONSTRAINT PK_Alias
PRIMARY KEY NONCLUSTERED (AliasUUID)
GO
ALTER TABLE [dbo].[Alias]
    ADD CONSTRAINT FK_AliasToRealPerson
    FOREIGN KEY (RealPersonUUID) REFERENCES dbo.RealPerson (RealPersonUUID)
GO
ALTER TABLE [dbo].[Alias]
    ADD CONSTRAINT CK_RealPersonUUID_PriorityRank_Unique UNIQUE (RealPersonUUID,PriorityRank)
GO
ALTER TABLE [dbo].[Alias]
    ADD CONSTRAINT CK_PriorityRank_Range CHECK (PriorityRank >= 0 AND PriorityRank < 33)
GO
if exists (select * from dbo.sysindexes where name = N'IX_Alias_RealPersonUUID' and id = object_id(N'[dbo].[Alias]'))
    DROP INDEX [dbo].[Alias].[IX_Alias_RealPersonUUID]
GO
CREATE INDEX IX_Alias_RealPersonUUID] ON [dbo].[Alias  
GO
GRANT SELECT , INSERT, UPDATE, DELETE ON [dbo].[Alias] TO public
GO
INSERT INTO dbo.RealPerson ( RealPersonUUID , MyCompanyFriendlyUniqueIdentifier )
select '11111111-1111-1111-1111-111111111111' , 'ABC'
union all select '22222222-2222-2222-2222-222222222222' , 'DEF'
INSERT INTO dbo.[Alias] ( RealPersonUUID , LastName, FirstName , PriorityRank)
select '11111111-1111-1111-1111-111111111111' , 'Smith' , 'Joseph' , 0
union all select '11111111-1111-1111-1111-111111111111' , 'Smith' , 'Joey' , 1
union all select '11111111-1111-1111-1111-111111111111' , 'Smith' , 'Joe' , 2
union all select '11111111-1111-1111-1111-111111111111' , 'Smith' , 'Jo' , 3
union all select '22222222-2222-2222-2222-222222222222' , 'Doe' , 'Jane' , 0
select 'Main Identity' as X, * from dbo.RealPerson rp join dbo.[Alias] al on rp.RealPersonUUID = al.RealPersonUUID where al.PriorityRank = 0
select 'All Identities' as X, * from dbo.RealPerson rp join dbo.[Alias] al on rp.RealPersonUUID = al.RealPersonUUID
select 'Aliai Only' as X, * from dbo.RealPerson rp join dbo.[Alias] al on rp.RealPersonUUID = al.RealPersonUUID where al.PriorityRank > 0

desse

赞同来自:

首先,您应该识别您的实体。很明显,你有一个人,每个人都有自己的身份。它们是独一无二的,应该保持这样。然后你有别名的他们应该在他们自己的表中与一对多的关系。这应该与主键,forgien键,适当的快速查找索引一起使用。每个表都需要一个聚簇索引来提高性能。然后,您应该使用存储过程来返回或更新表。我故意使用某些词,因为如果你谷歌他们,你会得到很多关于你需要做什么的好信息。