Hi,

Im new to Hibernate so bear with me. Im trying to implement a set of custom providers for use in ASP.NET. I have mappings for ApplicationUsers to Applications this is working fine. Im getting a bit confused when trying to implement the RoleProvider functionality.

Basically I have 3 entities Application, ApplicationUser and Role. The schema needs to allow users to be members of many applications and to be in different roles for EACH application.

My area of confusion stems from the User to Role relationship. Below is my table design:

CREATE TABLE ApplicationUser_Role (
ApplicationId int NOT NULL,
UserId int NOT NULL,
RoleId int NOT NULL,
PRIMARY KEY (ApplicationId, UserId, RoleId));

CREATE TABLE ApplicationUser_Application (
ApplicationId int NOT NULL,
UserId int NOT NULL,
PRIMARY KEY (ApplicationId, UserId));

CREATE TABLE Role_Application (
ApplicationId int NOT NULL,
RoleId int NOT NULL,
PRIMARY KEY (ApplicationId, RoleId));

CREATE TABLE Role (
Id int NOT NULL AUTO_INCREMENT,
Name VARCHAR(255) NOT NULL,
LoweredName VARCHAR(255) NOT NULL,
Description VARCHAR(255) NULL,
PRIMARY KEY (Id));

CREATE TABLE ApplicationUser (
Id int NOT NULL AUTO_INCREMENT,
Name VARCHAR(255) NOT NULL,
LoweredName VARCHAR(255) NOT NULL,
Description VARCHAR(255) NULL,
Password VARCHAR(128) NOT NULL,
PasswordFormat int NOT NULL,
PasswordSalt VARCHAR(128) NOT NULL,
Email VARCHAR(128) NOT NULL,
LoweredEmail VARCHAR(128) NOT NULL,
PasswordQuestion VARCHAR(255) NULL,
PasswordAnswer VARCHAR(255) NULL,
Comments VARCHAR(3000) NULL,
IsApproved bit NOT NULL,
IsLockedOut bit NOT NULL,
CreationDate datetime NOT NULL,
LastActivityDate datetime NOT NULL,
LastLoginDate datetime NOT NULL,
LastLockedOutDate datetime NOT NULL,
LastPasswordChangeDate datetime NOT NULL,
FailedPwdAttemptCnt int NOT NULL,
FailedPwdAttemptWndStart datetime NOT NULL,
FailedPwdAnsAttemptCnt int NOT NULL,
FailedPwdAnsAttemptWndStart datetime NOT NULL,
PRIMARY KEY(Id));

CREATE TABLE Application (
Id int NOT NULL AUTO_INCREMENT,
Name VARCHAR(255) NOT NULL,
LoweredName VARCHAR(255) NOT NULL,
Description VARCHAR(255) NULL,
PRIMARY KEY(Id));

As you can see ive defined a ApplicationUser_Role Table which maps a User - Application - Role. The thing is im not sure how to map this 3-way relationship. Can someone give me a nudge in the right direction. I am not committed to this table design so if you think it can be redone, then im happy to do that.

Many thanks,