Hi, I have a database that looks like:

table: tbl_project
Fields:
id
projectName
description
moduleId [FK: tbl_module(id)]


table: tbl_module
Fields:
id
moduleName
description

table: tbl_projectmodule
Fields:
id
projectId [FK: tbl_project(id)]
moduleId [FK: tbl_module(id)]

tbl_module and tbl_project are mapped to corresponding classes Module and Project. tbl_projectmodule table is not mapped to any class.

My problem is :-
I want to design a class 'ProjectAndModule' that has following functions:
1) function that returns a list of projects containg a particular module
E.g: if "Sale" is a module then the function should return all the projects that have the module "Sale" in it.

2) A function that returns the list of modules present in a project.
Eg: If 'X' is a project then the function should return all the modules in the project 'X'.

What is the best way of acheiving this...

I am confused because I donot know how to use the new class 'ProjectAndModule' with the 'Project' and 'Module' class

I would request help on Mapping scheme to be used and also the query to be used in the new class.

on the projet mapping file declare
<bag name="modules" table="tbl_projectmodule" cascade="all">
<key column="projectId"/>
<many-to-many column="moduleId" class="module"/>
</bag>
you'll be able to do myProjet.getModules() --> return the list


on the projet mapping file declare
<bag name="projects" table="tbl_projectmodule" cascade="all">
<key column="moduleId"/>
<many-to-many column="projectId" class="project"/>
</bag>
you'll be able to do myModule.getProjects() --> return the list

http://www.hibernate.org/hib_docs/refer ... tions-s1-5

Now optimize it with lazy attribute, secure it with cascase attribute...

on the projet mapping file declare
<bag name="modules" table="tbl_projectmodule" cascade="all">
<key column="projectId"/>
<many-to-many column="moduleId" class="module"/>
</bag>
you'll be able to do myProjet.getModules() --> return the list


on the module mapping file declare
<bag name="projects" table="tbl_projectmodule" cascade="all">
<key column="moduleId"/>
<many-to-many column="projectId" class="project"/>
</bag>
you'll be able to do myModule.getProjects() --> return the list

http://www.hibernate.org/hib_docs/refer ... tions-s1-5

Now optimize it with lazy attribute, secure it with cascase attribute...

you don't need a 'ProjectAndModule' class since the association table doesn't have any extra info for example...

Hi there,
Thanks very much for the reply...
As u said I donot require the third table tbl_projectmodule becase this table doesnot have any extra information.

but, lets say this table has some extra information like

tbl_ProjectModule

Fields:
i. projectId [FK: tbl_project(id)]
ii. moduleId [FK: tbl_module(id)]
iii. User [FK: tbl_user(id)]
iv. userRole


How would I desing a class mapping for this table...

What is the design stratergy that I should be using.

Please Note: This is just a samle table, My intention is to understand the design stratergy to be used in cases where a table holds Many-to-Many relation with extra information.