Introduce integerity in the database
-
I have a single table called customers that stores all customers information. It has only one primary id field, which is identity. At times it happens duplicate records are inserted. But since the primary ID is the different, the records are actually different. There is no other field in the table that I can make unique. What is the best way to introduce data integrity in the database so that duplicate records can not be inserted in the table. Note that table has more than 80 fields.
-
I have a single table called customers that stores all customers information. It has only one primary id field, which is identity. At times it happens duplicate records are inserted. But since the primary ID is the different, the records are actually different. There is no other field in the table that I can make unique. What is the best way to introduce data integrity in the database so that duplicate records can not be inserted in the table. Note that table has more than 80 fields.
If you cannot make any other single column unique, then you need to make a combination of columns unique. For instance, you could choose to reject entries that have the same name that are at the same address. The problem with this approach is that you will have to decide how close the selection would be i.e would
johnson PLC,e 123 Albert stbe the same asJohnson Private Limited Company, 123 albert street. That is where your requirements and business process will need to come into play. Hope this helpsWhen I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
-
If you cannot make any other single column unique, then you need to make a combination of columns unique. For instance, you could choose to reject entries that have the same name that are at the same address. The problem with this approach is that you will have to decide how close the selection would be i.e would
johnson PLC,e 123 Albert stbe the same asJohnson Private Limited Company, 123 albert street. That is where your requirements and business process will need to come into play. Hope this helpsWhen I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
Well this would be interesting if I can do what you mention. I can use it for other purposes and will be helpful to me. But in my situation, I can not make a combination of columns unique. Things are like 1. Customer are renewed so the data can be very similar to each other. 2. Some honest mistakes can occur where the same data is added twice. The user can figure it out but I want to stop it from the db end. I am thinking I should change an existing field unique or add a new field which would be unique.
-
I have a single table called customers that stores all customers information. It has only one primary id field, which is identity. At times it happens duplicate records are inserted. But since the primary ID is the different, the records are actually different. There is no other field in the table that I can make unique. What is the best way to introduce data integrity in the database so that duplicate records can not be inserted in the table. Note that table has more than 80 fields.
One way to stop duplicate records being inserted is to check and see if a record for a customer already exists before inserting the record. For example, something along the lines of:
if (not exists(select top 1 1 from Customers where Field1 = @Param1 and Field2 = @Param2 ... and so on))
begin
insert into Customer (...)
values (...)
end"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
-
I have a single table called customers that stores all customers information. It has only one primary id field, which is identity. At times it happens duplicate records are inserted. But since the primary ID is the different, the records are actually different. There is no other field in the table that I can make unique. What is the best way to introduce data integrity in the database so that duplicate records can not be inserted in the table. Note that table has more than 80 fields.
sharp_k wrote:
it happens duplicate records are inserted
How do you determine that? To go along with what the others said, you can query the table for similar entries and present them to the user, e.g.: You entered "John Smith at 123 Main St", is this the same customer as "John Smith at 123 North Main Street"?
-
Well this would be interesting if I can do what you mention. I can use it for other purposes and will be helpful to me. But in my situation, I can not make a combination of columns unique. Things are like 1. Customer are renewed so the data can be very similar to each other. 2. Some honest mistakes can occur where the same data is added twice. The user can figure it out but I want to stop it from the db end. I am thinking I should change an existing field unique or add a new field which would be unique.
sharp_k wrote:
But in my situation, I can not make a combination of columns unique.
The table represents a "customer". There are going to be columns in there, not all 80, which defines what a unique "customer" is. You CANNOT procede until you determine which columns make it unique. If there a few columns then you can add a uniqueness constraint. But lets say you have a 'lot' of columns, like 50 columns, then you are probably out of luck for easy solutions because it is unlikely that you can add a uniqueness constraint for that many columns. In that case you would need to wrap ALL access to table in a proc. The proc would verify, via a query, that no other record existed with those 50 columns before the insert. Views can often help with this. You also need to consider exactly how those records get added. Because now the system is going to start producing errors where it didn't produce errors before. Additionally if you have a 'lot' of columns which make it unique then for something called a "customer" I would think that there is a design problem.
-
I have a single table called customers that stores all customers information. It has only one primary id field, which is identity. At times it happens duplicate records are inserted. But since the primary ID is the different, the records are actually different. There is no other field in the table that I can make unique. What is the best way to introduce data integrity in the database so that duplicate records can not be inserted in the table. Note that table has more than 80 fields.
-
One way to stop duplicate records being inserted is to check and see if a record for a customer already exists before inserting the record. For example, something along the lines of:
if (not exists(select top 1 1 from Customers where Field1 = @Param1 and Field2 = @Param2 ... and so on))
begin
insert into Customer (...)
values (...)
end"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
-
Well this would be interesting if I can do what you mention. I can use it for other purposes and will be helpful to me. But in my situation, I can not make a combination of columns unique. Things are like 1. Customer are renewed so the data can be very similar to each other. 2. Some honest mistakes can occur where the same data is added twice. The user can figure it out but I want to stop it from the db end. I am thinking I should change an existing field unique or add a new field which would be unique.
Like others have said, you need to normalize your database. When a customer is renewed you don't get a new customer and should therefore not get a new entry in the customer table, but rather a new entry in the subscription table or customer history table or whatever table makes most sense to your system. As we don't know much about you database we can only give you generalized advice. Mine would be to read this[^] article. It describes in an easy to understand way how to, and why, you normalize your database.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
Indeed it could.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me