0

I have a SellerProduct table. Each row within the table represents product information as offered by a seller. The SellerProduct table has the following columns:

id (serial, pk)
productName (nvarchar(50))
productDescription (ntext)
productPrice (decimal(10,2))
sellerId (int, fk to Seller table)

A product may be the same across sellers, but the productName, productDescription and productPrice can vary per seller.

For example, consider the product TI-89. Seller A may have the the following information for the product:

productName = TI-89 Graphing Calc
productDescription = A graphing calculator that...
productPrice 65.12

Seller B may have the the following information for the product:

productName = Texas Instrument's 89 Calculator
productDescription = Feature graphing capabilities...
productPrice 66.50

Admin users will be required to identify that products are the same across various sellers.

I need a way to capture this information (i.e. products are the same across sellers). I could create another table called SellerProductMapper as follows:

sellerProductId1 (int, pk, fk to SellerProdcut table)
sellerProductId2 (int, pk, fk to SellerProdcut table)

The problem with this approach is that it permits sellerProductId1 and sellerProductId2 to be from the same seller for a given row. That should not be allowed.

How can I capture this many-to-many relationship while enforcing this constraint?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
James
  • 2,876
  • 18
  • 72
  • 116
  • "A product may be the same across sellers". What do you mean by this? Are you saying that you could have the same productName, productDescription with a different sellerID? Or are you saying that two different products could have the same function but are competitors? If the former then you have a serious normalization problem. If the latter, then I'm not sure how you are identifying something as the "same". –  Jan 29 '16 at 20:06
  • Sorry... I edited the original post to include an example. Hopefully, it will better clarify. – James Jan 29 '16 at 20:35

2 Answers2

0

You need something that you don't currently have: a "Product Identity" table. If I were designing it, it would have a product ID, Manufacturer's product code, and manufacturer's description. Then the entries in SellerProduct would reference the seller and the product, and you could enforce the constraint with a unique index on the combination of seller and product.

Alan Hadsell
  • 470
  • 2
  • 7
0

You are coming across your issue because you actually have a more serious data problem with how your table design is laid out.

  • Your id field does not uniquely identify your data; Making sure every column is dependent on this field is paramount to proper normalization. You should never be in the situation where you need a human pair of eyes to identify two different pieces of data which actually represent the same thing. If I had to guess that id field is probably just an incremented key... ditch this for a truly unique identifier... such as composite key of the manufacturer and the manufacturer's serial number so you know you cannot have two of the same product

  • Your sellerID field belongs in a different table entirely. A product is just that... a single entity which represents an object. A seller on the other hand is a separate entity that provides a product for sale. Since a seller can have many products and a product can be sold by many sellers, you need a bridge entity (aka a composite entity) to eliminate the many-to-many relationship. If you split the SellerID info from your product table you will have something like this:

Product Table

    serialnumber   pk
    manufacturer   pk
    productName
    productDescription

SellerProducts Table (bridge entity between product and seller)

    sellerID       pk
    manufacturer   pk
    serialnumber   pk
    Price

Seller Table

    sellerID     pk
    Name
    Location
    Other seller based info, etc...

This information is more normalized with productName and productDescription dependent on the primary key of the Product table and price dependent on the primary key of the SellerProducts table.

Unfortunately, cleaning up your data will most likely prove to be tedious... but unless you address this normalization issue now, your problems will only keep compounding until the database is impossible to maintain.

  • Thanks. Unfortunately, the products that we are maintaining do not have a clear way to id them such as serialnumber + manufacturer. This is why an admin user is required to look at the seller information provided for a product (e.g. product name / description) and determine if it is the same product that is being provided by another seller. – James Jan 29 '16 at 21:36