0 Replies Latest reply on Aug 2, 2006 7:59 PM by fhh

    Dealing with not normalized data

    fhh Expert

      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