Dublication of rows
-
Hi All, I am storing details of many hotels in a database. Details are coming from different providers. And we store it into our database. Now sometimes, details about the same hotel come from different providers. And each provider may use different ID or name to refer a hotel. So when we enter this details we end up entering dublicate rows. For example. Provider A give the details of InterContinental hotel and they refer the hotel with the ID "Inter". Provider B also give the detail of the same hotel but they refer that hotel with the ID "InterCon". So after saving the details of InterContinental provided by the provider A when I store the detail given by provider B I need to make out that details of InterContinental is already stored and so I should not store it second time. But as the IDs are different how do I make it sure. More over I dont have any other ID etc. Can any one give me answere quickly. Regards, Vaibhav
-
Hi All, I am storing details of many hotels in a database. Details are coming from different providers. And we store it into our database. Now sometimes, details about the same hotel come from different providers. And each provider may use different ID or name to refer a hotel. So when we enter this details we end up entering dublicate rows. For example. Provider A give the details of InterContinental hotel and they refer the hotel with the ID "Inter". Provider B also give the detail of the same hotel but they refer that hotel with the ID "InterCon". So after saving the details of InterContinental provided by the provider A when I store the detail given by provider B I need to make out that details of InterContinental is already stored and so I should not store it second time. But as the IDs are different how do I make it sure. More over I dont have any other ID etc. Can any one give me answere quickly. Regards, Vaibhav
Hi Vaibhav, Question 1: What would be the matching details of a particular hotel, which would help us to determine, whether the two records submitted by the two providers are of the same hotel? Question 2: How can you be sure, that the hotel "Inter" sent by Provider A is the same as the hotel "InterCon" submitted by Provider B. If the address of "Intercon" is the same as that of "Inter", except for an extra comma, how can you determine that both the records are of the same hotel, programmatically? Question 3: What is/are the field/s that is/are common and unchangeable for both the records? As fas as your question goes. If we assume that only the id changes, and the rest of the details remain the same, then you can design the database this way..
**tblHotel** ---------- HOTEL_ID (pk) (IDENTITY) HOTEL_NAME HOTEL_ADDRESS and so on for the unchanging fields. -----------another table would be there,**tblProviderHotel** ------------- PROVIDER_HOTEL_ID (pk) identity (just a key field) HOTEL_ID (fk from tblHotel) PROVIDER_HOTEL_ID (this would be the id, given by the providers, "inter", "intercon" etc) PROVIDER_ID (fk from Provider table if you want) -------------Now in this scenario (inter and intercon scenario), tblHotel would contain just one record for the hotel details, whereas the tblProviderHotel would contain two records with the same HOTEL_ID (from tblHotel) but different PROVIDER_HOTEL_IDs (inter and intercon) Similarly, if a provider C sends you a record of the same hotel, but the id is differnt, say "Inter-Conti", then you can check if the rest of the fields such as HOTEL_ADDRESS, CITY, ZIP etc matches with any record in tblHotel and if it does as in thsi case, then take the HOTEL_ID for the matching record from the tblHotel and just make a new entry in the tblHotelProvider table, with the id that the provider gave you.. So now, you have 1 record in tblHotel, but 3 related records in tblHotelProvider all pointing to one hotel, but submitted by different providers.. Also this is implemented, assuming all the other details are matching for all the 3 hotel records provided by the providers..as I asked you earlier. Please do let me know if this helps.. Cheers, Rahul I do not understand what I do. For what I want to do I do not do, but what I hate I do. - Romans 7:15 -
Hi Vaibhav, Question 1: What would be the matching details of a particular hotel, which would help us to determine, whether the two records submitted by the two providers are of the same hotel? Question 2: How can you be sure, that the hotel "Inter" sent by Provider A is the same as the hotel "InterCon" submitted by Provider B. If the address of "Intercon" is the same as that of "Inter", except for an extra comma, how can you determine that both the records are of the same hotel, programmatically? Question 3: What is/are the field/s that is/are common and unchangeable for both the records? As fas as your question goes. If we assume that only the id changes, and the rest of the details remain the same, then you can design the database this way..
**tblHotel** ---------- HOTEL_ID (pk) (IDENTITY) HOTEL_NAME HOTEL_ADDRESS and so on for the unchanging fields. -----------another table would be there,**tblProviderHotel** ------------- PROVIDER_HOTEL_ID (pk) identity (just a key field) HOTEL_ID (fk from tblHotel) PROVIDER_HOTEL_ID (this would be the id, given by the providers, "inter", "intercon" etc) PROVIDER_ID (fk from Provider table if you want) -------------Now in this scenario (inter and intercon scenario), tblHotel would contain just one record for the hotel details, whereas the tblProviderHotel would contain two records with the same HOTEL_ID (from tblHotel) but different PROVIDER_HOTEL_IDs (inter and intercon) Similarly, if a provider C sends you a record of the same hotel, but the id is differnt, say "Inter-Conti", then you can check if the rest of the fields such as HOTEL_ADDRESS, CITY, ZIP etc matches with any record in tblHotel and if it does as in thsi case, then take the HOTEL_ID for the matching record from the tblHotel and just make a new entry in the tblHotelProvider table, with the id that the provider gave you.. So now, you have 1 record in tblHotel, but 3 related records in tblHotelProvider all pointing to one hotel, but submitted by different providers.. Also this is implemented, assuming all the other details are matching for all the 3 hotel records provided by the providers..as I asked you earlier. Please do let me know if this helps.. Cheers, Rahul I do not understand what I do. For what I want to do I do not do, but what I hate I do. - Romans 7:15Hi Rahul, First of all, thanx for giving it a try. What would be the matching details of a particular hotel, which would help us to determine, whether the two records submitted by the two providers are of the same hotel? Ans 1: There is nothing to be match by which we can be sure that the two hotels provided by different providers are same, otherewise problem should not arise :) Ans 2: This is again second way of asking first question. You cant be sure that the "Inter" by provider A is same as the hotel "InterCon" by provider B. If you can assure that then porblem should not arise :) Ans 3: Address etc are given by both providers but there is no garuntee that from both the providers you will get the same address string for the same hotel. As one can write "Street" while other can use "St." instead of. Vaibhav.