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 data model shown in Figure 1 and you can download it from Dropbox as a PDF.
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.
Then, I have set up a PRIMARY KEY (PK) in the
ProductSeller associative entity which is composed of
ProductNumber, and these attributes are, in turn, FOREIGN KEY references to
Product.ProductNumber, respectively. This is the place where you could store product names, descriptions and prices as defined by every specific seller.
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 the
OrganizationId PK from
SellerProduct, and then assign it a role name 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
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
- 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
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
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.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
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
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
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
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
You can then employ a comparable approach to handle the changes related to the
ProductSeller price datum.
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.