I have quite often come upon tables likes this:
supplier articlenr size stock
------------------------------------------
SuppA 100 1 57
SuppA 100 2 63
SuppA 100 3 19
SuppB 100 1 12
SuppB 100 2 21
SuppB 100 3 51
So supplier and article number is the composite key of the table.
I want to map it to an entity that looks something like this:
public class ArticleSupply {
private ArticleSupplyID id;
private Collection<SupplyInfo> supply;
// ArticleSupplyID holds supplier and articlenr
@Id @Embedded
public ArticleSupplyID getId() {
return id;
}
//This has size and stock information
@OneToMany
public Collection<SupplyInfo> getSupply() {
return supply;
}
}
(I know the code above is not correct but I hope it gives you the idea.)
Obviously this is not possible since the datamodel is not normalized. This raises two problems:
1.) The ArticleSupplyID is not unique in the table.
2.) Both ArticleSupply and SupplyInfo are mapped to the same table.
One way to deal with the situation is by creating a view for ArticleSupply which looks something like this: SELECT DISTINCT supplier, articlenr FROM mytable. Then you could use a composite key for SupplyInfo to get the collection from the original table.
This should work (untested) but it is not a very elegant solution. Since not normalized data is so common there must be a better solution.
Does anybody have an idea?
Regards
fhh
