I have a bunch of data from multiple retailers. Each product that each retailer sells may or may not be sold by another retailer in the database. If multiple retailers sell the same product, then the product can be identified by a SKU. I currently have 1 database for each retailer. I'm having a problem trying to conceptualize an appropriate database schema to identify how many retailers are selling the same product.

Should I have 1 table with all retailers?


Yes I would make 1 table with rows for each retailer and a separate table with one row for each product. Then I'd just do another table with rows that have 2 foreign keys: 1 for the retailer and 1 for the product (many-to-many). To find out how many retailers are selling a product just count the number of rows that have the foreign key for a specific product in the many to many table.

  • 1
    +1. If the price per product can change per retailer then that can go in the intersection table too. So queries such as "who's the cheapest retailer for product X?" will be nice and simple. – sfinnie Aug 12 '11 at 7:36
  • What if the data formats are going to be different. Is there any way to do this with 1 table per retailer? – Ryan Aug 12 '11 at 7:58
  • depends what you mean by "different data formats". Yes there are ways to cope: but if you don't hold the price uniformly then querying becomes more difficult. Maybe you could expand...? – sfinnie Aug 12 '11 at 12:52
  • 1
    @user660943: Reading between the lines, I get the impression you believe data you get from the retailers must be inserted as you receive it. There's no need for that. There's a certain subset of information you care about across all of the retailers; everything else is chaff. Write one program per retailer that reads whatever data they give you, picks out what you want and inserts it in your database. If you start splitting it into one table per retailer, you're asking for complexity that you don't really need. – Blrfl Aug 12 '11 at 13:45
  • @user660943 If most of the data is the same between retailers, but there are a few unique fields that could change over time as you add retailers you might want to think about making a column that contains serialized schemaless data. That way you can have all the common data in searchable columns, but any unique data fields can be added on the fly with no schema changes. – Seth Archer Brown Aug 12 '11 at 15:40

In answer to your question the very last thing you want to do is separate out the data by retailer if you need to compare retailers. There should be commonality of data for the most important things. If the files from each retailer are different that is just a matter of transforming the data to your format. It is best to ask them to provide ina common format. If a retailer has some addtional information that the others do not you can either add that as an nvarchar(max) field that is retailerdescription or add a related table for that retailer that is only used when calling up the details about that product (not for comparisons). Or you can add extra fields that allow nulls to handle the data that some reatilers provide that others do not.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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