my user's table in the database is becoming increasingly larger (in terms of columns not rows) and as a consequence is slowing down various areas of my site. This is because it tries to grab every column from the user's table everytime it does a join against it.
I figured i would keep all the common fields in the user's table and then put the additional fields in seperate tables. For example, say i have the following tables in my database:
Users:
- UserID (PK, Identity)
- UserName
- Password
...
UsersActivity:
- UserID (PK, FK)
- LastActivityDate
- LastLoginDate
...
UsersPreferences:
- UserID (PK, FK)
- HtmlEmail
- HideEmail
...
With the following entities:
public class User {
public virtual int UserID { get; set; }
public virtual string UserName { get; set; }
public virtual string Password { get; set; }
public virtual UserActivity Activity { get; set; }
public virtual UserPreferences Preferences { get; set; }
}
public class UserActivity {
public virtual User User { get; set; }
public virtual DateTime LastActivityDate { get; set; }
public virtual DateTime LastLoginDate { get; set; }
}
public class UserPreferences {
public virtual User User { get; set; }
public virtual bool HtmlEmail { get; set; }
public virtual bool HideEmail { get; set; }
}
I was just wondering what is the best way to map this for optimum performance? I figured i could do a one-to-one mapping on the Activity and Performance properties in the User entity. However as far as i understand one-to-one mapping doesn't support lazy loading and this approach would end up being slower.
I also looked into component mapping and wasn't too sure whether i could map this into a seperate table (please correct me if it would be better to keep it in the same table) and whether components supported lazy loading.
Before i go about doing some heavy refactoring of my application i thought i would get the opinion of someone who might have done this. Really appreciate the help.
Thanks
Edit: I found that you could lazy load a one-to-one relationship as long as it is required/constrained. Which it is my case. Therefore i went ahead and carried out the instructions in the following article:
http://brunoreis.com/tech/fluent-nhibernate-hasone-how-implement-one-to-one-relationship/
The trouble now is that i get the error:
NHibernate.Id.IdentifierGenerationException: NHibernate.Id.IdentifierGenerationException: null id generated for: UserActivity.
