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

Question

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?

Can a person have more than 1 alias? Can an alias be "reused" for multiple people? — granadaCoder, Apr 11 '13 at 16:59
Yes, a person can have multiple aliases. *Can an alias be "resused" for multiple people?* I don't see any reason why not, but I think that is an implementation detail. — mwolfe02, Apr 11 '13 at 18:30
You should decide. It is the difference between a 1:N or a M:N relationship (on the question is the same alias can be reused for different people). — granadaCoder, Apr 11 '13 at 19:05
I'm not sure I understand what you are asking. Either you are overcomplicating it or I am undercomplicating it (in my head). Either way I think the conversation would be best served by your posting an answer and picking one approach or the other (the one that makes the most sense to you). — mwolfe02, Apr 11 '13 at 19:42
What are you trying to do? Are you saying Joseph Smith can "be" Joseph Smith, Joe Smith and Joey Smith? Are Joe and Joey "real" or just place holders? — granadaCoder, Apr 11 '13 at 21:06
In my sample above, Joseph Smith, Joe Smith, and Joey Smith are all the same actual person. This single person has been added to the database in different forms. Each "virtual version" of Mr. Smith must remain intact. For example, Mr. Smith buys parcel A. On the deed, he is listed as Joseph Smith. He later buys parcel B. On that deed, he is listed as Joe Smith. And parcel C is deeded as Joey Smith. There is a legal requirement to maintain a link to the actual name on the deed. But if we run a query for all of Mr. Smith's parcels, it should return parcels A, B, and C. Make sense? — mwolfe02, Apr 11 '13 at 21:48

score 1 · Answer 1 · answered Apr 11 '13 at 22:14

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

Wow, that is a thorough answer. I won't be able to test it until tomorrow, but +1 for sheer effort and time spent. I appreciate it. — mwolfe02, Apr 12 '13 at 01:14
So this is basically the same approach as my original one with two key differences: 1) switch the names of the tables (this does seem like an improvement conceptually) and 2) add a priority field. I'd like to leave the question open for a bit to see if it generates any more responses, but it looks like I will probably go with something very similar to this. Thanks. — mwolfe02, Apr 12 '13 at 13:21
You could have LastName and FirstName (the primary one) in the RealPerson table. But that just feels redundant to me. You may be tempted to have a "IsPrimary" bit field. But some super dba back in the day .. chastised me when I did that once. I wouldn't say it is wrong, but "IsPrimary" doesn't have that "set based feeling" to me. PS I don't have any indexes on the LastName, FirstName (or other), those indexes will depend on how you will do your searches I guess. Dont' quote me though, I'm not a "search by string(s)" expert. Heck, I'm not an expert at all, I just play one on TV. — granadaCoder, Apr 12 '13 at 13:31
I might change the "Alias" object name to "KnownIdentity". Again, not right or wrong. Just a preference. — granadaCoder, Apr 12 '13 at 13:48

score 0 · Answer 2 · answered Apr 11 '13 at 16:17

0

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.

answered Apr 11 '13 at 16:17

Ian P

1,724
1
10
12

I'm not sure how this addresses my question. I fully understand all of the concepts you mentioned. I was specifically asking if my *model itself* was optimal, not how to implement the model I already have. Thanks. – mwolfe02 Apr 11 '13 at 16:19
Well, it depends. If there is (just one) alias for every person, then no, you would increase the data dispersion by having two tables. On the other hand if this was true but the overwheming number of data access commands only returned the contents of the first table you whould have a net increase: not as much as correct use of covering indexes however. It also depends on the size of the data. So it depends on why you want an alias and its future use, which you may not know. I really recommend inside SQL http://sqlblog.com/blogs/kalen_delaney/archive/tags/SQL+Server+2012/default.aspx – Ian P Apr 15 '13 at 07:46

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

2 Answers2