Hi!

Is the following idea possible using Hibernate/mysql 5:

My hibernate objects:

Object DVD
- Title
(- Collection of UserDVDs)

Object UserDVD
- DVD
- User
- pricePayed

Object User
- Username

There is a one-to-many association between DVD->UserDVD (is this required at all?) and a many-to-one association between UserDVD -> DVD.

I want to display all DVD items from the DVD table to the user logged on and the user should do specific search queries on the DVD table. For each DVD found I want to show the DVD title and wheter the user owns that DVD (displaying the price he paid for it) or not (so he can buy it) in a single table.

Is this use case possible using the (detached) criteria api resulting in one single SQL query? Any ideas are highly appreciated :-).

Cheers,

Chris

Hi chris,

send us your code what ever you tried.
.

Hi dharmendra,

this is more a design question I want to answer before writing any code. I don't have useful code at the moment.

Thanks,

Chris

Its really quite simple.

Ask you this question.

Can a user have multiple DVD's?
Can a DVD have multple user's?

If yes on both, then you will have to create a many-to-many table.
If no on one of them, then you will create a one - to - many.

If a user can have many dvd's and only one dvd per user then it is better to create to tables where userid is a foreign key in dvd.

If this helped, please give credit.

cash1981, basically you are right. A user can have multiple DVD and a DVD can have mulitple users. Modeling this relationship by many-to-many might be an option.

But how does this perform? I have about 40.000 DVDs, 30.000 users and 2.000.000 User-DVD-Relationships. A DVD can have 15.000 users or more. I don't think it is a good idea to have a collection of users (DVD owners) in the DVD object, is it?

My objective is to optimize as much as possible the following request:

A user searches for a DVD (e.g. by its title etc.) and in the list of matching DVDs, each DVD the logged in user already owns (= a User-DVD-relationship exists) is marked or not.

The question how to model this domain and how to query it is the key for a high performant system. Every idea is highly appreciated.

Thanks,

Chris