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

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.

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.
SellerProductMapper
has two columns,sellerProductId1
andsellerProductId2
.