0
votes

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?

2
Can a person have more than 1 alias? Can an alias be "reused" for multiple people? - granadaCoder
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
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
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
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

2 Answers

1
votes

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
0
votes

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.