locked
Unique ID field concatenated from two other fields RRS feed

  • Question

  • I would like to create a table from scratch, or insert into an existing table a Unique client id that consists of let's say a clients last name and the the last 4 of their phone number. All I can find is documentation on adding an auto-incremented numeric field.

    So I guess I should ask for an opinion as well, what is the best practice to create a unique client id for a clients table?

    Wednesday, July 16, 2014 2:15 PM

Answers

  • Best practice is to avoid storing duplicative information - PERIOD.  You appear to forget or ignore that names can change as can phone numbers - and which phone number (since most people and organizations have multiple) do you intend to use? 

    Next, what purpose is to be served by this column?  You use the term "client id", but how exactly do you intend to use it?  Your use of "unique" implies that you might intend to use it as a primary key.  I'm certain you can find many discussions about the use of natural keys vs. artificial keys, so there is no need to rehash that here.  Your proposal is a combination of both approaches - which leads me back to "why"?

    You propose to create a new table or alter an existing table.  Those are 2 very different scenarios and require very different approaches to evaluate the impact of your proposal.  Good design isn't something you just throw together, despite the impression you might get from the forums.  You need to understand the system that you are modeling, the entities in that system, what your goals are with your database and how you intend to use it.  If you have an existing table, you need to understand how it is used, its relationships to other entities, how robust the code that references/uses that table is, etc.  Adding a column (or a unique constraint) can easily bring a working system to a halt - and a table named client is generally an important and oft-referenced table. 

    Lastly, just to reinforce my first comment.  The mobile phone of a friend was recently stolen.  This person will replace that phone and, for security reasons, get a new phone number.  Now how exactly does your proposal prevent this person from being added as a client multiple times in your table?  The short answer is it does not.  Unfortunately this is one instance where it is difficult to find a good natural key that can be effectively enforced. Therefore, this situation is generally handled by using a completely synthetic value automatically generated by the database.  Sql server provides identity and sequence for that purpose.

    • Proposed as answer by tracycai Monday, July 28, 2014 6:29 AM
    • Marked as answer by tracycai Wednesday, July 30, 2014 8:23 AM
    Wednesday, July 16, 2014 2:58 PM
  • >Unique client id that consists of let's say a clients last name and the the last 4 of their phone number

    That may not be UNIQUE. Email is unique unless shared by two people like husband & wife.

    Best to use SURROGATE PRIMARY KEY:

    1. INT IDENTITY

    2. INT - filled from SEQUENCE object (SS 2012 and on)

    Place a UNIQUE KEY on the columns which represent the NATURAL KEY. Sometimes this maybe difficult.  Client name + phone number would make a fair composite NATURAL KEY.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012









    • Edited by Kalman Toth Wednesday, July 16, 2014 4:03 PM
    • Marked as answer by tracycai Wednesday, July 30, 2014 8:23 AM
    Wednesday, July 16, 2014 4:01 PM

All replies

  • Hi Flyersfan9474,

    If you have a large clients table or as it gets larger this logic may cause problems with uniquity.  

    If you are trying to get a unique client ID but you don't want them to be incremental you might try the GUID data type passing in a newid().

    To answer your question directly you can look into a computed columns.    Or if you have the users email address they have to be unique. 


    Wednesday, July 16, 2014 2:53 PM
  • Best practice is to avoid storing duplicative information - PERIOD.  You appear to forget or ignore that names can change as can phone numbers - and which phone number (since most people and organizations have multiple) do you intend to use? 

    Next, what purpose is to be served by this column?  You use the term "client id", but how exactly do you intend to use it?  Your use of "unique" implies that you might intend to use it as a primary key.  I'm certain you can find many discussions about the use of natural keys vs. artificial keys, so there is no need to rehash that here.  Your proposal is a combination of both approaches - which leads me back to "why"?

    You propose to create a new table or alter an existing table.  Those are 2 very different scenarios and require very different approaches to evaluate the impact of your proposal.  Good design isn't something you just throw together, despite the impression you might get from the forums.  You need to understand the system that you are modeling, the entities in that system, what your goals are with your database and how you intend to use it.  If you have an existing table, you need to understand how it is used, its relationships to other entities, how robust the code that references/uses that table is, etc.  Adding a column (or a unique constraint) can easily bring a working system to a halt - and a table named client is generally an important and oft-referenced table. 

    Lastly, just to reinforce my first comment.  The mobile phone of a friend was recently stolen.  This person will replace that phone and, for security reasons, get a new phone number.  Now how exactly does your proposal prevent this person from being added as a client multiple times in your table?  The short answer is it does not.  Unfortunately this is one instance where it is difficult to find a good natural key that can be effectively enforced. Therefore, this situation is generally handled by using a completely synthetic value automatically generated by the database.  Sql server provides identity and sequence for that purpose.

    • Proposed as answer by tracycai Monday, July 28, 2014 6:29 AM
    • Marked as answer by tracycai Wednesday, July 30, 2014 8:23 AM
    Wednesday, July 16, 2014 2:58 PM
  • Wow Guys! Very valid points! This is the information I was looking for. It's clear that my initial approach had some holes. Just to be clear, this is a db I am writing from scratch and using Lightswitch as a front end. I have 3 tables ClientInfo, EventInfo, and EventPayments. The clientID will relate the clientinfo and event info tables and eventId will link EventInfo to EventPayments. So I suppose I will have sql generate the clientid randomly, but the eventid will be a combo of clientid and randomly generated number again. Does this sound more logical?

    A guid is most likely not what I am looking for because if memory serves correctly it is a combination of mac address and time stamp. That would be too long of a client id to give to someone or put on a contract. So with that said, I have the all  tables created, not related yet,and not populated. What sql code do i use to insert another column to generate a random number?

     
    Wednesday, July 16, 2014 3:27 PM
  • >Unique client id that consists of let's say a clients last name and the the last 4 of their phone number

    That may not be UNIQUE. Email is unique unless shared by two people like husband & wife.

    Best to use SURROGATE PRIMARY KEY:

    1. INT IDENTITY

    2. INT - filled from SEQUENCE object (SS 2012 and on)

    Place a UNIQUE KEY on the columns which represent the NATURAL KEY. Sometimes this maybe difficult.  Client name + phone number would make a fair composite NATURAL KEY.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012









    • Edited by Kalman Toth Wednesday, July 16, 2014 4:03 PM
    • Marked as answer by tracycai Wednesday, July 30, 2014 8:23 AM
    Wednesday, July 16, 2014 4:01 PM