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

database-designjavajpaobject-orientedobject-oriented-design

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

It must maintain history of data changes to a product
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.

Best Answer

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.

Related Solutions

Java Design Patterns – How to Use Decorator and Factory Together to Extend Objects

Yes, SpecialTable can be produced by the same factory (and by the same factory method) that produces Table. This not a problem, as long as SpecialTable provides at least the methods guaranteed by the Table interface (which it does).

This sort of situation makes sense when objects returned from the factory have some guaranteed behaviour/functionality (interface Table), and some optional stuff (interface SpecialTable).

So, in order to make use of the optional extra abilities/stuff available on some concrete objects returned by the factory, you have a few choices:

use polymorphism (recommended)
make use of instanceof (or similar), which can be used to check for interface compliance/exact typing (avoid this if possible!)

Using polymorphism

An example to illustrate: suppose SpecialTable has extra data and an extra widget 'blarg' which appears on the UI representing special tables. Do you really need the code which uses SpecialTable to know that it needs to add 'blarg' to the UI? Or can you just have your external code ask the thing of type Table to add its necessary UI components to some UI container? Then the calling code doesn't care what exact type the thing is -- SpecialTable deals with it, adding the 'blarg' to the container itself. This pattern can be described as the delegate pattern (also sometimes also known as strategy pattern).

Using instanceof (type checking)

Example of instanceof use:

// Table and SpecialTable are interfaces
Table table = TableFactory.instance().getDefaultTable();

if (table instanceof "SpecialTable") {
  SpecialTable specialTable = (SpecialTable)table;
  specialTable.specialMethod();
}

table.normalMethod();

Note that when you define your SpecialTable interface you can declare it as extending the Table interface and then only list the extra methods.

Your factory will be producing concrete objects of either type. So you might have a concrete class called BoringTable which implements Table, and a concrete class called ExcitingTable which implements SpecialTable.

Beware instanceof!

It's generally better to avoid things like 'instanceof' (or generally speaking, ifs of any kind which depend on what type an object is). You should favour polymorphism instead. By this I mean having multiple different classes, of a common type, enclosing different behaviour in their implementation code.

Ask yourself this: does the class using something of type Table (which might also be of type SpecialTable) need to care if it's a special table or not? Can you not hide that distinction in some sort of implementation of the SpecialTable -- have it do something different, something extra, that plain old Table does?

Database Design – Best Multiple Tables Product with Categories

First things first, I'm not sure I agree with your choice of one table per product type. I'm not saying it's wrong, but... It could cause you a lot of grief down the road. It's a fragile schema. You know best. Just get some rest and think on it a bit more.

Regarding category -> sub category, the most straightforward plan of attack is probably two tables with a relationship table that links a product. Using your convention:

Category:
int: id
str: name

SubCategory:
int: id
str: name
fk: Category_id

ProductSubCategory:
fk: product_id
fk: SubCategory_id

So, a category might be computers. Sub-categories are ultrabooks, workstations, and tablets. A product might be a Samsung tablet. Assign said product to the "tablets" sub-category via the relationship table. From that, you can determine it's also a "computer".

If you're lazy and you're 100% sure your schema won't change, you can just drop the SubCategory_id directly on a product. No relationship table required. That means you can only ever have one sub-category, though. Not sure if that's what you want.

In another universe, consider a self-referencing category table. That allows any number of sub-category levels:

Category:
int: id
str: name
fk: Category_id (null if top level)

The data:

id: 1, name: "Computers", Category_id: null
id: 2, name: "Laptops", Category_id: 1
id: 3, name: "Tablets", Category_id: 1
id: 4, name: "Ultrabooks", Category_id: 2
id: 5, name: "Chromebooks", Category_id: 2

If products can be in multiple bottom-level sub-categories, use a relationship table. Otherwise, just drop the id on the product table. Easy squeezy.

Best Answer

Related Solutions

Java Design Patterns – How to Use Decorator and Factory Together to Extend Objects

Database Design – Best Multiple Tables Product with Categories

Related Topic