Hi,

I am new to Hibernate. I am using hibernate with a database which is not pretty much normalized. I have written the database design as below.


Datatypes

Activity

ActivityId Integer
ActivityName Varchar(50)
AssignTo Integer
TeamAssignment Bit


UserInfo

UserId Integer
LoginId Varchar(30)
FirstName Varchar(20)
LastName Varchar(20)


Team

TeamId Integer
TeamName Varchar(30)


UserTable

UserId Integer
TeamId Integer


In the system there are Activities and these activities can be assigned to either a UserId or a TeamId. If Activity is assigned to UserId then TeamAssignment should be 0. Otherwise if Activity is assigned to a TeamId then TeamAssignment should be 1. You can see that there is no relationship defined in Activity Table. And there is no possibility that I can make a relationship. As AssignTo can be either a UserId or TeamId. Problem is that in my query I have to give only UserId and I have to find all Activities which are related to that UserId. So there are two scenarios.

1) TeamAssignment is 0 :
Means Activity is AssignTo a UserId.

2) TeamAssignment is 1 :
Means Activity is AssignTo a TeamId. Here I have to check that UserId given to me is also a member of that team. If yes then that Activity should also be picked.

In SQL this problem can be solve with many ways but how this problem can be catered in HQL?