Given the following three tables describing an item that can be either a toy, a food or unknown:
CREATE TABLE item (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
type VARCHAR(255) NOT NULL CHECK (type IN ('TOY', 'FOOD', 'UNKNOWN'))
);
CREATE TABLE item_toy (
item INTEGER NOT NULL REFERENCES item(id),
color VARCHAR(255) NOT NULL,
material VARCHAR(255) NOT NULL
);
CREATE TABLE item_food (
item INTEGER NOT NULL REFERENCES item(id),
smell VARCHAR(255) NOT NULL,
taste VARCHAR(255) NOT NULL
);
Toy items would have only toy properties, food items only food properties and uknown items neither of them.
We can model these tables in Java, using Spring Data JDBC, in the following way:
public class Item {
private int id;
private String name;
private ItemType type;
private ItemToy toyProperties;
private ItemFood foodProperties;
}
public enum ItemType {
TOY,
FOOD,
UNKNOWN
}
public class ItemToy {
private String color;
private String material;
}
public class ItemFood {
private String smell;
private String taste;
}
public class ItemRepository extends CrudRepository<Item, Integer> {
// ...
}
The problem I have with this implementation is that the fields toy and food may be null or not regardless of the value of type: ideally I would like unknown items to not have any property, toy items to only have toyProperties and food items to only have foodProperties.
Is there a more elegant way to model this relationship, either by changing the database structure and/or Java classes, using inheritance or composition? How would the repository class/es look like in that case?
