CODEWITHSUNDEEP
sqlsql-serverdatabase-designdata-modelingself-reference
mwolfe02
mwolfe02

Reputation: 24237

Implementing an Aliases table (self-referencing many-to-many)

I am trying to model an Alias relationship. That is, several records in my person table may represent the same actual person. I don't care who the "Primary" person is. All Person records would carry equal weight.

I have implemented this in the past with the two tables you see below.

-------------    ------------
| Person    |    | Alias    |
|-----------|    |----------|
| PersonID  |    | AliasID  |
| LastName  |    | PersonID |
| FirstName |    ------------
-------------

Here is some sample data:

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)

I suppose I could move the AliasID to the Person table since there is a 1-to-1 relationship between the PersonID fields. However, I may want to add additional fields to the Alias table (like Sequence number, etc.) at some point in the future.

Is there a better way to model this than what I have here?

Upvotes: 0

Views: 796

Answers (2)

granadaCoder
granadaCoder

Reputation: 27904

This is how I would do it.

--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]([RealPersonUUID])  
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

Upvotes: 1

Ian P
Ian P

Reputation: 1724

First, you should identify your entities. Clearly you have a person and each person will have their own identity. They are unique and should allways be kept as such. Then you have Alias's They should be in their own table with a one to many relationship. This should be enfrced with primary keys, forgien keys, indexes for quick lookup where appropriate. Each table need a clustered index also for performance. You should then use stored procedures to return or update the tables. I've intentionally used certain word, because if you google them, you will get lots of good information on what you need to do.

Upvotes: 0

Related Questions