Class Design for JPA Entities with Multiple Tables Referring to Same Business Object

Question

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:

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.

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