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

Question

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?

score 8 · Accepted Answer · edited Jun 15 '20 at 09:05

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.

Figure 1 - Products and Sellers Data Model -Option A

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.

2. 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.

Figure 2 - Products and Sellers Simplified Data Model - Option B

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.

3. 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).

4. 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 related to product prices), I deem necessary the inclusion of a mechanism to track these changes. This is the reason for which I incorporated the entity types denominated ProductHistory and ProductSellerHistory in the above presented logical model.

In this respect, you may find this post about temporal databases and this post about data versioning of relevance.

4.2 Handling ProductPrice as a time series

You might be face the need to follow the trail of time-related product modifications exclusively when they concern to prices, so you could alternatively make use of the model depicted in Figure 3, which you can download from Dropbox as a PDF, as well.

Figure 3 - Products and Sellers Data Model - Option B

In this way, let us take the Product and ProductPrice entity types as references. As demonstrated in this alternative model, I have moved the DefaultPrice attribute to a new entity, and this new entity has a PK made up of ProductNumber (also playing the role of a FK that makes a reference to Product) and CreatedDateTime (that would mark the exact point in time in which a determined default price was inserted).

Consequently, every time that a default price value needs to be put up-to-date, your database users only have to INSERT a new ProductPrice row, containing the suitable ProductNumber taken from Product and the new price value. You may use a server function so that you can retrieve and store the exact insertion instant, i.e. CreatedDateTime, in a reliable manner.

The current or effective version of a product default or base price occurrence would be the row holding the MAX(ProductPrice.CreatedDateTime) for a definite ProductNumber, and all the other rows (when they exist) matching the same ProductNumber would be the previous DefaultPrice versions.

You can then employ a comparable approach to handle the changes related to the ProductSeller price datum.

Notes

_{1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the theoretical papers authored by the originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship theory, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown. It is worth noting that this techinque was formalized by way of first-order logic.}

_{2. IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.}

_{3. The use of role names is recommended since 1970 by Dr. Codd in his seminal paper entitled “A Relational Model of Data for Large Shared Data Banks”. For its part, IDEF1X —keeping fidelity regards relational practices— also advocates role naming.}

score 3 · Answer 2 · answered Jan 29 '16 at 21:40

My first reaction is that your primary key is not sufficient to guarantee uniqueness. I think you should consider a composite key of ProductId, SellerId. Even then you could still have duplicates in the table. I think you really need to backup and look at the real business requirements. For example just how "faithfully" do you need to capture unique products. Many manufacturers produce essentially the same product under a profusion of SKU's (look at Toshiba's Laptops sometime) and sometimes provide different SKU's to different distributors/retailers.

Also, as an aside you probably don't want NText for your Production Description. Surely a few hundred or few thousand characters would be sufficient.

score 0 · Answer 3 · answered Jan 29 '16 at 21:40

This is why many industries enforce SKU, UPC or any other unique identifier. If you don't have one, you could create one and let them enter this information in the database. Once the information is there then it will be easy to run the queries you need.