Saving data to table without a primary key

Question

I have a schema that looks like this

User

Id INT
Username VARCHAR(100)
Password VARCHAR(100)

User_Info_Key

Id INT
Description VARCHAR(100)

User_Info

User INT
Info_Key INT
Info_Value VARCHAR(100)
PRIMARY KEY(User, Info_Key)

Here's my user mapping file:

<class name="User" table="[user]">
  <id name="Id">
    <column name="id" />
    <generator class="native" />
  </id>
  <property name="Username" />
  <property name="Password" />
  <bag name="InfoValues" cascade="all" lazy="false">
    <key column="[user]" />
    <one-to-many class="FMInfoValue" />
  </bag>
</class>

<class name="InfoKey" table="[user_info_key]">
  <id name="Id">
    <column name="id" />
    <generator class="native" />
  </id>
  <property name="Description" />
</class>

<class name="InfoValue" table="[user_info]">
  <composite-id>
    <key-property name="User" column="[user]" type="int"></key-property>
    <key-property name="Key" column="[info_key]" type="int"></key-property>
  </composite-id>
  <property name="Value" column="[info_value]" />
  <many-to-one name="FMInfoKey" class="FMInfoKey"
             column="[info_key]"
             cascade="all"
             lazy="false" />
</class>

When I create a new user I do this:

User newuser = new User();
newuser.Username = this.Username;
newuser.Password = this.Password;
session.Save(newuser);

And this saves the base information.

But when I try to add the first name (info key value 1) and last name (info key value 2) nothing is saved to the database:

InfoValue userfname = new InfoValue();
InfoKey userfnamekey = new InfoKey();
userfnamekey.Id = 1;
userfname.InfoKey = userfnamekey;
userfname.Key = 1;
userfname.User = (int) newuser.Id;
userfname.Value = this.Firstname;
session.Save(userfname);

InfoValue userlname = new InfoValue();
InfoKey userlnamekey = new InfoKey();
userlnamekey.Id = 2;
userlname.InfoKey = userlnamekey;
userlname.User = (int)newuser.Id;
userlname.Value = this.Lastname;
session.Save(userlname);

How do I get NHibernate to save the two info values to the info value table?

If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Mar 18 '11 at 23:02

score 2 · Accepted Answer · edited May 23 '17 at 11:55

First, I think what you want to do is having two many-to-one properties in your InfoValue class. That would look like this:

class InfoValue
{
    public virtual User User { get; set; }
    public virtual InfoKey Key { get; set; }
    public virtual String Value { get; set; }
}

Your mapping would look something like this:

<class name="InfoValue" table="[user_info]">
  <composite-id>
    <key-many-to-one name="User" column="[user]" lazy="proxy" class="User" />
    <key-many-to-one name="Key" column="[info_key]" lazy="proxy" class="InfoKey" />
  </composite-id>
  <property name="Value" column="[info_value]" />
</class>

Example taken and adapted from here: Composite-id with many-to-one

----- Possibly unwanted advice -----

Second, if I may be so bold to offer some advice, do not do this. I am referring to your table structure. If my assumption is wrong, feel free to ignore my advice.

I assume that you want to put the user information in an extra table in order to avoid having to modify the table structure if and when new properties are required. If that is the reason, I can honestly tell you from my own experience that this will lead to a lot of headache. Apart from the performance issue when querying the tables everything else will also just be much more complicated. Your example of inserting values speaks for itself. Any queries where you search for those properties will just be as bad. What will you do if you want to save anything but Strings? Casting to int, DateTime, bool, etc.?

I could go on and on here, but unless you absolutely cannot alter the table structure later on, please do not pursue this idea.

Saving data to table without a primary key

1 Answers1