1

I have an application that works with products from various external data sources (in DataSourceProduct) and it also maintains its own version of the product (in MasterProduct). Here's the DB schema:

DB Schema

Note: DataSourceProduct has ten other columns not shown in the diagram. The MasterProduct has ten other columns not shown in the diagram. For example These column have the same name in each table. that have the same name but for brevity are not shown in the diagram. The common columns are not on the Product table but are on the DataSourceProduct

Each external data source may provide its own representation of a given product. The productId (i.e. Product.id) is an auto generated (incrementing) id in the database. It's a surrogate key. A productId should refer to 1 & only 1 real world product. Each data source may have its own data about that product and the DB must capture that. Suppose we have an iron door. If it's the first product inserted into the DB it will be given a productId of 1. Now, data source A may give a description of "fine door". Data source B may give the same iron door a description of "old iron door". So, DataSourceProduct will have 2 rows (1 row for each data source). Both rows will have productId = 1 so that we can know that the both data sources are actually referring to the same product.

Unfortunately, there is no data (such as SKU) that can determine that a product from data source A and data source B refer to the same product. Users will manually map the products as being the same. The Product table's productId serves the purpose of storing the user mapping (i.e. id which products from various data sources are referring to the same product).

The application also needs to have its own version of product data which is stored in the MasterProduct table. It differs from DataSourceProduct because

  1. It must maintain history of data changes to a product
  2. It must maintain vendor data

The application's product (i.e. MasterProduct) will be used extensively throughout the application whereas the external data source's product (i.e. DataSourceProduct) is only used in one small part of the application.

How should I design the corresponding JPA entities? The three tables representing products may (?) make sense from a DB perspective. In OOP though, it seems like an inheritance relationship exists.

  • I'm a little confused. What does the productID value actually signify here? Are you saying that a single product can have multiple productID values depending on which description is used? – Andrew Apr 26 '17 at 19:13
  • Are the common fields of the product located in Product table? Or It's just a table for relationships? For the second case it',s just unecessary because It looks like a one-to-one relationship. The name also could lead to confusion. – Laiv Apr 26 '17 at 19:27
  • @Andrew, the productId is an auto generated (incrementing) id in the database. It's a surrogate key. A productId should refer to 1 & only 1 real world product. Each data source may have its own data about that product and the DB must capture that. Suppose we have an iron door. If it's the first product inserted into the DB it will be given a productId of 1. Now, data source A may give a description of "fine door". Data source B may give the same iron door a description of "old iron door". So, DataSourceProduct will have 2 rows (1 row for each data source). Both rows will have productId = 1. – James Apr 26 '17 at 19:49
  • 1
    @James Then I would change the name to the table because It's confusing. It's rather a SKU than a Product. Also many-to-one relationship can be implemented with two tables only. The table Product, IMO, is adding complexity to the model. – Laiv Apr 26 '17 at 19:59
  • 1
    @James I'm not sure I agree with Laiv about condensing to two tables. It seems that the DataSourceProduct table (which may be misnamed) embodies the relationship between the dataSource and the Product. I assume the product table contains many other details about the product, other than the DataSource-associated name? Anyway I would probably create objects for each of these tables and define the relationships between them using JPA notation. I don't see much inheritance since they each contain their own relevant fields -- which is probably why you should rename. – Andrew Apr 26 '17 at 20:59
1

I think your confusion stems from the Product table's general lack of utility, since it contains an ID and nothing else.

However it's not a big deal. When defining JPA entities, I would start with the Products table and define each of the child objects as separate classes. Then include those in the Product class:

@Entity
@Table(name = "PRODUCT")
public class Product {

   @Id
   @Column(name = "id")
   private long id;

   @OneToMany(mappedBy="product")
   List<DataSourceProduct> dataSourceProducts;

   @OneToMany(mappedBy="product")
   List<MasterProduct> masterProducts;
}

Each of these child products would be define with the reverse @ManyToOne relationship with the Product table.

As you mention in the comments, you might rename these and maybe shift around some of the properties, but I think this structure should be a good starting point.

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.