2

I have a contact table which stores all the contact information of a person such as name, address, phone number.

The system would be more user friendly if it can prevent users from accidentally entering duplicate records.

If it is unique it will be more user friendly, but it costs performance to check, plus the added code increases the maintenance cost in the future.

If it's not necessary from a business requirement standpoint, have you found person/contact records to be worth maintaining uniqueness in? Can you think of any problems I might run into enforcing uniqueness on them?

Improve this question
11
  • Can you make some fields unique - such as the phone number, if you are sure that no two customers will share the same 'phone number? Otherwise, SELECT before INSERT and, if a matching record already exists, ask the user who is inserting it what to do
    – Mawg says reinstate Monica
    Dec 2, 2015 at 14:09
  • 1
    @Mawg Having two customers share a phone number is a very common scenario, although mobile phone numbers are of course, less likely to be shared...
    – Robbie Dee
    Dec 2, 2015 at 14:23
  • Yup, I thought so - which is why I said "such as ..". Maybe social security number would be better ..
    – Mawg says reinstate Monica
    Dec 2, 2015 at 14:38
  • @Mawg social security numbers are not guaranteed to be unique, at least not in every country. I don't think there's enough context in this question to provide a meaningful answer. What data is the OP storing? Why does it matter for users to be unique? The answers to these questions are use case specific.
    – toniedzwiedz
    Dec 2, 2015 at 20:15
  • 2
    @Mawg the assignment of social security numbers in Poland earlier in the 20th century used to be done by post and the process was not as well automated as one might hope. Duplicate numbers are known to have been assigned and it's not safe to assume every person has a unique one. I learned this while working on a medical application.
    – toniedzwiedz
    Dec 3, 2015 at 18:35

3 Answers 3

Reset to default
2

Possibly, but if these contact records are going to be entered by employees, it is perfectly possible that you could have a difference in address/name/phone number styles that would make this difficult. E.g. for address:

1 High Street  
1, High Street  
1 High St

This applies across columns too:

Joe Smith|1 High Street
Jo Smith|1 High St

I'd also consider storing phone numbers (perhaps even addresses) in a separate table from the contact since a contact can have more than one number.

Your interface can assist greatly here too (assuming you have one). Address lines, names and phone numbers can be broken up into their constituent parts and validated.

Improve this answer
3
  • The breaking up of address lines, phone numbers or names into parts is likely to cause problems if you need to deal with international contacts. There is simply too much variation across the globe.
    – Bart van Ingen Schenau
    Dec 2, 2015 at 11:40
  • @BartvanIngenSchenau Well indeed - a global solution for this would be a considerable piece of work.
    – Robbie Dee
    Dec 2, 2015 at 11:59
  • Another possibility is to use some form of address checking API. It's the automated equivalent of typing someone's address into google maps before you send the letter, to make sure you have the right zip code. (Except that I think automating it through google maps would violate their TOS.) The USPS has such an API.
    – Wildcard
    Dec 3, 2015 at 20:24
0

I have a table contact which store all contact information of a person name, address, phone number. The contact records will be entered by some employees.

I want to know that should I check for duplication when a contact record is saved?

Define "unique".

Each and every "Contact" [record] should have a unique identifier which. IMO, cannot be any of the things you mention (they can all change over time). This identifier should be application-generated, probably meaningless, and should not change from the moment the record is created to the moment that it is finally destroyed (systems like this are unlikely to physically delete records very often; keeping long-term records of people is a very common legal requirement these days).

So yes, your records should be uniquely identified, so that you only have one record per Person (without this constraint, your table will be of little practical use).

Given this, your question then becomes one of finding out whether or not you've seen this particular "Person" before - this is a De-Duplication issue and, again, is very common to systems like this.

One way to tackle it might be to provide a "Search" facility that people can use to locate a Person before creating a new record for them.
Alternatively, you'll need to run numerous database queries looking for data items that are "like" the ones entered; addresses are poorly standardised in the way that they are entered and so will be problematical to match. Phone numbers are easier so long as you have a standardised format for storing them.

If you find your queries containing lots of "like" clauses, especially those with leading wildcards, then you're probably heading in the general direction of poor application performance.

And, even after all of this, remember that you will probably have to create the "sledgehammer" process that "merges" two Contact records - and every other record in every other table that relates to them - into one.

Improve this answer
1
  • Thank you! I gonna make unique of combination columns so that an exception will be thrown if duplication occurs, then I can catch it and show a message to user. I think columns with various format for the same value like address should not be included in the combination
    – Mai Hữu Lợi
    Dec 4, 2015 at 5:00
0

For those who skip the comments, but read the answers, here is a copy/paste of a comment thread above, which seems to satisfy the OP. Please note, I am not asking to be awarded the answer, just to make this exchange more visible to future readers.

Can you make some fields unique - such as the phone number, if you are sure that no two customers will share the same 'phone number? Otherwise, SELECT before INSERT and, if a matching record already exists, ask the user who is inserting it what to do – Mawg

@Mawg Having two customers share a phone number is a very common scenario, although mobile phone numbers are of course, less likely to be shared... – Robbie Dee

Yup, I thought so - which is why I said "such as ..". Maybe social security number would be better .. – Mawg

@Mawg social security numbers are not guaranteed to be unique, at least not in every country. I don't think there's enough context in this question to provide a meaningful answer. What data is the OP storing? Why does it matter for users to be unique? The answers to these questions are use case specific. – toniedzwiedz

@Mawg that would be a great solution, a group of field would be unique such as Name+email+phone number so that I can prevent the employee from accidentally entering duplicated record. – Mai Hữu Lợi

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.