79019

Nhibernate composite key question

Question:

I have a table called person_skills like so:

person_id, skill_type_id, base_score, misc_score

There is a lookup table that contains id, name for skill_types.

Now the tricky thing is that I have a composite key for person_id, skill_type_id. There will be many entries within this table as a person may have 5 skills.

Currently I have got a class like so:

public class skill { int BaseScore {get;set;} int MiscScore {get;set;} }

Then I have a class to contain all this like below:

public class person_skills { int person_id {get;set;} IDictionary<skill_type, skill> skills {get;set;} }

Now im not sure if this is the best way to handle this relationship, ultimately I need to be able to give people a link to skills, there is one person to many skills.

I was thinking about just putting in an auto incrememnt id column and use that as the PK, but it doesn't seem ideal. I can change the models and the DB if required, but as this is used within an ajax part of a page I need to be able to change the skills and then update them into the database.

Answer1:

I did not find an actual question but I'll answer anyway. :)

You do not need a surrogate key for the person_skills table. Your composite key, consisting of person_id and skill_type_id, should be sufficient. I believe the following classes and mappings reflect what you are trying to accomplish here.

Classes:

public class Person { public virtual int PersonId { get; set; } public virtual String Name { get; set; } public virtual IList<PersonSkills> Skills { get; set; } } public class SkillType { public virtual int SkillTypeId { get; set; } public virtual String SkillName { get; set; } public virtual IList<PersonSkills> Persons { get; set; } } public class PersonSkills { public virtual int PersonId { get; set; } public virtual int SkillTypeId { get; set; } public virtual int BaseScore { get; set; } public virtual int MiscScore { get; set; } public override bool Equals(object obj) { if (ReferenceEquals(this, obj)) { return true; } if (obj == null || !(obj is PersonSkills)) { return false; } PersonSkills o = obj as PersonSkills; return (this.PersonId == o.PersonId && this.SkillTypeId == o.SkillTypeId); } public override int GetHashCode() { int hash = 13; hash = hash + this.PersonId.GetHashCode(); hash = hash + this.SkillTypeId.GetHashCode(); return hash; } }

Mappings: (FluentNhibernate)

public class PersonMap : ClassMap<Person> { public PersonMap() { Id(x => x.PersonId); Map(x => x.Name); HasMany(x => x.Skills) .KeyColumn("PersonId") .Cascade.All(); } } public class SkillTypeMap : ClassMap<SkillType> { public SkillTypeMap() { Id(x => x.SkillTypeId); Map(x => x.SkillName); HasMany(x => x.Persons) .KeyColumn("SkillTypeId") .Cascade.All(); } } public class PersonSkillsMap : ClassMap<PersonSkills> { public PersonSkillsMap() { CompositeId() .KeyProperty(x => x.PersonId) .KeyProperty(x => x.SkillTypeId); Map(x => x.BaseScore); Map(x => x.MiscScore); } }

Mappings (hbm, generated by FluentNHibernate - I removed output that is not required):

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" > <class xmlns="urn:nhibernate-mapping-2.2" name="Person" table="Person"> <id name="PersonId" type="int"> <column name="PersonId" /> <generator class="identity" /> </id> <bag cascade="all" name="Skills" mutable="true"> <key> <column name="PersonId" /> </key> <one-to-many class="PersonSkills" /> </bag> <property name="Name" type="String"> <column name="Name" /> </property> </class> </hibernate-mapping> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" > <class xmlns="urn:nhibernate-mapping-2.2" name="SkillType" table="SkillType"> <id name="SkillTypeId" type="int"> <column name="SkillTypeId" /> <generator class="identity" /> </id> <bag cascade="all" name="Persons"> <key> <column name="SkillTypeId" /> </key> <one-to-many class="PersonSkills" /> </bag> <property name="SkillName" type="String"> <column name="SkillName" /> </property> </class> </hibernate-mapping> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" > <class xmlns="urn:nhibernate-mapping-2.2" name="PersonSkills" table="PersonSkills"> <composite-id mapped="false" unsaved-value="undefined"> <key-property name="PersonId" type="int"> <column name="PersonId" /> </key-property> <key-property name="SkillTypeId" type="int"> <column name="SkillTypeId" /> </key-property> </composite-id> <property name="BaseScore" type="int"> <column name="BaseScore" /> </property> <property name="MiscScore" type="int"> <column name="MiscScore" /> </property> </class> </hibernate-mapping>

Recommend

  • MySQL performance using AUTO_INCREMENT on a PRIMARY KEY
  • WPF DataGrid lost focus after row delete
  • How do I control the soft menu button in Honeycomb?
  • Hudson dependencies
  • jinja2 template not found and internal server error
  • Divide a $1 by 3 and adjusting 1 cent
  • Spark (Scala) Writing (and reading) to local file system from driver
  • Find JSON nested nodes using multiple string values
  • Django model for a Postgres view
  • Android cannot disable cut copy paste
  • User messaging system
  • Bootstrap (v3.3.4) glyphicons not displayed in IE when refresh page (F5)
  • How do I display a dialog that asks the user multi-choice questıon using tkInter?
  • Combining two different ActiveRecord collections into one
  • Does Mobilefirst provide a provision to access web services directly?
  • How can I extract results of aggregate queries in slick?
  • Needing to do .toArray() to get output of mongodb .find() on key name not value
  • Groovy: Unexpected token “:”
  • How to use carriage return with multiple line?
  • Replace value with Factor in r data.table
  • MongoDB in PHP using aggregate to group by _id is null not working
  • How to access EntityManager inside Entity class in EJB3
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Linq Objects Group By & Sum
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • java.lang.NoClassDefFoundError: com.parse.Parse$Configuration$Builder on below Lollipop versions
  • Optimizing database types to compact database (SQLite)
  • JavaScriptCore crash on iOS9
  • Calling of Constructors in a Java
  • Traverse Array and Display in markup
  • Transpose CSV data with awk (pivot transformation)
  • Data Validation Drop Down Box Arrow Disappearing
  • Buffer size for converting unsigned long to string
  • KeystoneJS: Relationships in Admin UI not updating
  • Codeigniter doesn't let me update entry, because some fields must be unique
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • Turn off referential integrity in Derby? is it possible?
  • Does armcc optimizes non-volatile variables with -O0?
  • reshape alternating columns in less time and using less memory