SQL - Many-to-Many relationship with same table and with relationship constraint

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?

If I understand your scenario description correctly, you want to manage two different facts, so you should have (a) one table to store product data that is independent of sellers and (b) another table to retain product information that comes into play in the context of each relationship between products and their corresponding sellers.

1. Logical model

I have depicted such approach in the logical IDEF1X[1] data model shown in Figure 1 and you can download it from Dropbox as a PDF.

1.1 Product

As you can see in the referred model, you may use the Product entity type (or table at the implementation level) to store the default (or base) values of the product attributes. It can be useful to think about this entity as some kind of catalogue. Here is where you can keep the product data that does not depend on sellers.

I have denoted the Product PK as ProductNumber for expository porposes but, possibly, the use of some sort of ProductCode or another similar term might be more suitable or advantageous in your business domain.
1.2 ProductSeller

Then, I have set up a PRIMARY KEY (PK) in the ProductSeller associative entity which is composed of SellerId and ProductNumber, and these attributes are, in turn, FOREIGN KEY references to Party.PartyId and Product.ProductNumber, respectively. This is the place where you could store product names, descriptions and prices as defined by every specific seller.
1.3 Party

I decided to include the Party entity in case you have to store sellers that are either an Organization or a Person. These three entities are encompassed in a exclusive Supertype-Subtype relationship, so this post could be pertinent.
1.4 Implementation considerations

It is important to note that I indicated some attributes as ALTERNATE KEYS (AK plus its fitting number), since this implies that, when they become table columns, they should be set with a UNIQUE CONSTRAINT or UNIQUE INDEX.

As in any relational database development, you should seriously consider using ACID Transactions in order to guard your data integrity and consistency.

1. Simplified logical model

If in your case it is only possible for an Organization to become a Seller, then you can employ a structure like the one I propose in the logical model presented in Figure 2, and it is also downloadable from Dropbox as a PDF.

With this model, you just have to migrate[2] the OrganizationId PK from Organization to SellerProduct, and then assign it a role name[3] like SellerId in order to make it more meaningful or, maybe, leave it as OrganizationId if you feel more comfortable. After that, you migrate the Product.ProductNumber to complete the composition of the SellerProduct PK.

1. Data entry process

Having discussed the most relevant parts of the two suggested structures, I esteem opportune addressing the process that should take place when the data entry is being carried out. In this respect —with the aid of an application program that communicates with your database— your properly authorized users can:

• Inspect carefully the product information detailed by one certain seller.

• Compare it with the rows already contained in the Product table.

• If they define that such product instance has not been entered yet and therefore it is not provided by any seller, then they (1.1) INSERT a new Product row with the default or base values and also (1.2) a new row in the ProductSeller table including the suitable SellerId (drawn from Party.PartyId or Organization.OrganizationId) and the ProductNumber just created along with the remaining column values holding the product data as described by that seller in particular.

• If, on the contrary, they determine that the product under inspection is already stored in the catalogue and, perhaps, provided by one or more distinct sellers too, then they simply (1) INSERT a new row in the ProductSeller table including the corresponding SellerId and ProductNumber (taken from Product.ProductNumber) values toghether with the complementary column values that contain the product information as designated by the seller at hand.

So, as explained above, the ProductSeller.ProductNumber and ProductSeller.SellerId would be very useful to cover the following specification:

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

Since having a given ProductNumber value referenced from multiple ProductSeller rows will assist your database users in the identification and retrieval of those products that are offered by different sellers, who will be recognized by their distinctive SellerId value which, of course, points to a precise Party.PartyId (or Organization.OrganizationId).

1. Tracking Product data modifications

4.1 Product History and ProductSeller History

Since it seems reasonable to assume that product data can suffer modifications over time (and, probably, most of the time these modifications will be re

StackExchange Database Administrators Q#127767, answer score: 8