how to reconcile inconsistent data sources?

joelcannon · April 15th, 2012, 04:06 PM

I bought the kindle version of Begging Databese Design Solutions and immediately read it hoping I could find an answer to my problem, but while I filled in holes in my understanding of DB design, I am still struggling with a real world problem and hoping someone can offer some advice or direction.'

I have an online store that sells thousands of products. I buy these products from multiple suppliers with data feeds. Their data is full of errors, so It is difficult to cross-reference my SKUs with theirs. How do I reconcile the contradicting sources of data? It is an on going problem because products are added and discontinued by suppliers regularly.

For example, my product catalog has my SKU (primary key), a UPC (optional unique key) and a combination of manufacturer code and manufacturer part number (alternate candidate key).

The data from my suppliers always has their own variation of a SKU (primary key) which I try to cross-reference, but they often have a contradicting UPC , manufacturer part number or manufacturer (a result of historic corporate mergers and spinoffs). Sometimes I can algorithmically repair the data, or verify it by matching it with other sources that agree, but I still have a lot of inconsistent data. I imagine that anyone with multiple independent data sources has the same problem.

What does such a multi-datasource model look like?

How do I keep track of all the exceptions and manual corrections while building a cross-reference table?

I even have situations where a manufacturer will accidentally/intentionally reuse a UPC for more than one product or significantly change a product but keep the same part number. Not sure if it is worth incorporating an artificial key just for rare anomalies.

Advice?
Online references?
Books?

Rod Stephens · April 16th, 2012, 09:59 AM

Wow. Sorry but I don't know what to do about that.

I would try to clean the data as much as possible coming in so your database isn't full of garbage but it sounds like that will be really hard. You could start by making mapping tables to map bad vendor UPCs and SKUs to good ones but it they aren't consistent that will be hard.

Re Reusing UPCs and other keys, you could make a table that lists UPCs and effective dates, sort of like a temporal table for pricing. Or perhaps use UPC plus description or name as they key. It sounds like a major pain but I'm not sure what else you can do.

You could try pushing back and seeing if you can get vendors to clean thing up a bit but you'll need some leverage (like a really big purchaser would have) to get them to do anything.

Sorry I can't think of anything better.

joelcannon · April 16th, 2012, 10:46 AM

Your prompt response is helpful - it confirms that there is no simple solution that everyone but me knows about. I will continue to slog it out. This is where theory and the real world meet ;-)

btw- I found the book easy to digest and interesting to read. Well worth the money spent.

Rod Stephens · April 16th, 2012, 11:18 AM

If there is a perfect solution, then you and I may be the only ones who don't know about it.

I would post the question on other forums if you can, too. This one doesn't get as much readership as some others. I would at least try:

SQL Server Developer Center > SQL Server Forums > Database Design

You might also look at some of the other Microsoft forums. Go to Microsoft Developer Network > Forums Home and search for "database" to see what's available.

If you find something worthwhile, please post a link to it here as a followup. I'd like to know what people do about this, too. I'm sure you're not the only one out there.

Quote:

btw- I found the book easy to digest and interesting to read. Well worth the money spent.

Thanks for the kind words! Post a review when you have a chance.