Mapping denormalized schema w/ duplication entries?

[Tobias Trelle <to...@codecentric.de>]

Is it possible to map a denormalized schema with duplicate rows to a single
enitity instance? (Background: our DB admins DO NOT want a mapping table for
m:n association. They prefer one denormalized table at one end of the
association from the performance perspective).

Let's assume this minimal example: The entites A and B have a bidirectional
m:n association and have to be persisted in these tables:

CREATE TABLE A {
    A1 SMALLINT NOT NULL,
    A2 VARCHAR(50),
    PRIMARY_KEY(A1)
};

CREATE TABLE B {
    B1 SMALLINT NOT NULL,
    B2 SMALLINT NOT NULL,
    A_ID SMALLINT NOT NULL,
    PRIMARY_KEY(B1, A_ID)
};


The A side of the associtation is trivial:


@Entity
@Table(name = "A")
public class A {

    @Id
    private int a1;

    private String a2;

    @ManyToMany
    private Collection bs;


How to mapped the B side?


@Entity
@Table(name = "B")
public class B {

    @Id // ???
    private int b1;

    private int b2;

    @ManyToMany
    private Collection  as;


>From the logical view, only b1 makes up the PK but technical it has to be
(b1, a_id).

When I insert/delete/update a B referencing more the one A, how will OpenJPA
know to persist m rows in table B? To make things clearer, lets assume the
following data set:


|       A       |
+----+----------+
| A1 | A2       |
+----+----------+
|  1 | foo      |
|  2 | bar      |
+----+----------+

|       B        |
+----+-----------+
| B1 | B2 | A_ID |
+----+-----------+
|  3 | 42 | 1    |
|  3 | 42 | 2    |
|  4 | 12 | 1    |
|  4 | 12 | 2    |
+----+-----------+


When I add a new B ...


A a1 = ....findById(1);
A a2 = ...findById(2);
B b = new B();
b.setB1(5);
b.setB2(33);
b.setAs(new ArrayList ());
b.getAs().add(a1);
b.getAs().add(a2);
em.persist(b);


... how do I define a mapping that INSERTs two rows 


|  5 | 33 | 1    |
|  5 | 33 | 2    |


into table B? Of course, it's not only about the INSERT. All CRUD operations
and queries should honour the denormalization. Any ideas?

TIA,
Tobias

--
View this message in context: http://openjpa.208410.n2.nabble.com/Mapping-denormalized-schema-w-duplication-entries-tp6652021p6652021.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.