Self referencing a table

Question

Hello,

so I'm new to NHibernate and have a problem. Perhaps somebody can help me here. Given a User-class with many, many properties:

public class User
{
    public virtual Int64 Id { get; private set; }
    public virtual string Firstname { get; set; }
    public virtual string Lastname { get; set; }
    public virtual string Username { get; set; }
    public virtual string Email { get; set; }
    ...
    public virtual string Comment { get; set; }
    public virtual UserInfo LastModifiedBy { get; set; }
}

Here some DDL for the table:

CREATE TABLE USERS
(
"ID" BIGINT NOT NULL ,
"FIRSTNAME" VARCHAR(50) NOT NULL ,
"LASTNAME" VARCHAR(50) NOT NULL ,
"USERNAME" VARCHAR(128) NOT NULL ,
"EMAIL" VARCHAR(128) NOT NULL ,
...
"LASTMODIFIEDBY" BIGINT NOT NULL ,
) IN "USERSPACE1" ;

Database-table-field 'LASTMODIFIEDBY' holds for auditing purposes the Id from the User who is acting in case of inserts or updates. This would normally be an admin. Because the UI shall display not this Int64 but admins name (pattern like 'Lastname, Firstname') I need to retrieve these values by self referencing table USERS to itself. Next is, that a whole object of type User would be overkill by the amount of unwanted fields. So there is a class UserInfo with much smaller footprint.

public class UserInfo
{
    public Int64 Id { get; set; }
    public string Firstname { get; set; }
    public string Lastname { get; set; }
    public string FullnameReverse
    {
        get { return string.Format("{0}, {1}", Lastname ?? string.Empty, Firstname ?? string.Empty); }
    }
}

So here starts the problem. Actually I have no clue how to accomplish this task. Im not sure if I also must provide a mapping for class UserInfo and not only for class User. I'd like to integrate class UserInfo as Composite-element within the mapping for User-class. But I dont no how to define the mapping between USERS.ID and USERS.LASTMODIFIEDBY table-fields. Hopefully I decribes my problem clear enough to get some hints. Thanks alot!

Answer 1

This looks like a bad case or premature optimization.

Are you really having any performance issues if you define LastModifiedBy as User and use <many-to-one> to map it?

That should be your starting point.