I have inherited an existing table structure to represent a many-to-many relationship between Portfolios and Customers, and I want to represent this as simply as possible.
Because the occurrence table (CDB_PORTFOLIO_TO_CUSTOMER) has columns other than just the customerid and portfolioid, my only option for (directly) using a many-to-many seems to be with an "idbag", rather than a set (or another type).
I see a couple of possible issues with the idbag approach (which are not necessarily Hibernate issues either!):
1)No support for 3 the columns on the occurrence table ("date" and "curr" do not matter much to me here, because I want to get rid of those columns anyway), but... there is no support for the "portfoliotocustomertypeid" column, which represents the type of relationship between the customer and portfolio, and which is a fundamental attribute of the portfolio-customer relationship.
2)XDoclet does not seem to support "idbag", so I would have to create that portion of the .HBM.XML mapping file manually, and maintain it for any changes made.
3)idbag has no support for an "identity" id generator, and I presume it has no support for "assigned" either.. (although I could be wrong)
The possible atlternative that I am looking at is the following: breaking the many-to-many type relationship into 2 distinct one-to-many relationships in an attempt to make the problem more manageable. This would require an additional Java object to represent the occurrence table as an entity (Java class) in its own right. This will surely complicate the development of the model, and any subsequent queries run against it.
I guess my questions are the following:
1) Am I wrong in what I say here?
2) Am I missing any obvious fact, or is there another approach to this that I have not seen?
My table model is the following:
Code:
create table CDB_PORTFOLIO
(PORTFOLIOID int NOT NULL
-- other Portfolio level columns go here
)
create table CDB_CUSTOMER
(CUSTOMERID int NOT NULL
-- other customer level columns go here
)
create table CDB_PORTFOLIO_TO_CUSTOMER_TYPE
(PORTFOLIOTOCUSTOMERTYPEID int NOT NULL
,DESCRIPTION varchar(35) NOT NULL
,constraint CDB_PK_PORTFOLIO_TO_CUSTOMER_T primary key clustered (PORTFOLIOTOCUSTOMERTYPEID)
,constraint CDB_AK_PORTFOLIO_TO_CUSTOMER_T unique nonclustered (DESCRIPTION)
)
create table CDB_PORTFOLIO_TO_CUSTOMER
(PORTFOLIOTOCUSTOMERID int NOT NULL
,CUSTOMERID int NOT NULL
,PORTFOLIOID int NOT NULL
,PORTFOLIOTOCUSTOMERTYPEID int NULL
,DATE datetime NOT NULL
,CURR varchar(1) NOT NULL
,constraint CDB_PK_PORTFOLIO_TO_CUSTOMER primary key clustered (PORTFOLIOTOCUSTOMERID)
,constraint CDB_FK_PORTCUST_TO_CUST foreign key (CUSTOMERID) references CDB_CUSTOMER(CUSTOMERID)
,constraint CDB_FK_PORTCUST_TO_PORT foreign key (PORTFOLIOID) references CDB_PORTFOLIO(PORTFOLIOID)
,constraint CDB_FK_PORTCUST_TO_PORTCUST_TY foreign key (PORTFOLIOTOCUSTOMERTYPEID) references CDB_PORTFOLIO_TO_CUSTOMER_TYPE(PORTFOLIOTOCUSTOMERTYPEID)
)
Login
FAQ
Search
