How to map a table with 15 qty columns to a one to many rel?

fmcypriano · **Joined:** Tue Mar 13, 2007 8:26 am **Posts:** 25

Hello all,

I'm trying to map a legacy database, a very bad designed one by the way, to classes. What I want to do is map the database to classes, but I don't want to make the classes as wrong as the database and I'm wondering if it's possible to do what I describe bellow.

I've a Stock table which has 15 fields related to quantities, like this:

Code:

create table STOCK (
  ID INTEGER PRIMARY KEY,
  PRODUCT_ID INTEGER,
  COLOR_ID INTEGER,
  QUANT_01 DECIMAL(12,2),
  QUANT_02 DECIMAL(12,2),
  -- REPEAT UNTIL THE 15
  QUANT_15 DECIMAL(12,2)
);

Each quantity column represents the quantity of a kind, for example product "shoes" quant_01 means the quantity for size 7 and quant_02 means size 8.

I'm creating a new system that needs to use this table and I'd like to create classes without this terrible model. For example I'd like to create the following classes:

Code:

class Stock {
    private int id;
    private Produtct product;
    private Color color;
    private Set<StockQuantity> quantities;
}

class StockKind { // the shoes size
    private int id;
    private String description;
}

class StockQuantity {
   private Stock stock;
   private StockKind kind;
   private BigDecimal quantity;
}

I was thinking in a way to map the fields to StockQuantity and I'd like to know if anybody has any tips about how to accomplish this using Hibernate. What do you think, should I do this, any other ideas?

Regards

fmcypriano · **Joined:** Tue Mar 13, 2007 8:26 am **Posts:** 25

I made an almost-diagram to make this problem simpler to understand, see:

This image shows an example of row the data is in the database and how it should be in the classes. Each quant_?? column generates a new StockQuantity and StockKind object, the Stock object holds a collection with it's own quantities (StockQuantity class).

Is this possible to do using hibernate?