[jboss-user] [EJB 3.0] - Dealing with not normalized data

[fhh]

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

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3962675#3962675

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3962675