4

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?

  • I apologize for the delay. This fell off my radar when other project demands occurred. Not a good excuse though given your efforts and others to help...Sorry. FYI - I made a few comments and marked your answer as the solution. – user4321 Feb 29 '16 at 20:20
  • No worries. Best regards. – MDCCL Feb 29 '16 at 20:45
  • I'm having trouble understanding sellerProductId1 (int, pk, fk to SellerProdcut table). What are the columns for SellerProductmapper? – JohnOsborne May 29 at 17:32
  • In the OP, the SellerProductMapper has two columns, sellerProductId1 and sellerProductId2. – user4321 May 29 at 21:10
4

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.

  • 2
    Awesome! Very detailed and helpful response! We only have organizations so figure 2 works. We also have a temporal aspect in regards to pricing as you surmised. So, figure 4.2 is very relevant and useful for that. – user4321 Feb 29 '16 at 20:16
  • @James I'm glade to make a contribution. Your scenario is very interesting (and I consider that some points can be re-used in some other situations), so it required a proper response. Cheers! – MDCCL Feb 29 '16 at 20:44
3

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.

  • 1
    SellerProduct.id is defined as serial (i.e. auto incrementing). It is a surrogate key and is guaranteed to be unique. It does not refer to the product id. It refers to the SellerProduct id. It is very important to the business to capture products offered by sellers and match which products are the same among different sellers. Although I used a TI calculator as an example, there is no SKU (or other unique way to id) for these type of products. Admins have to do this. +1 - Thanks for the NText suggestion. – user4321 Feb 29 '16 at 16:46
0

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.

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.