I am using NHibernate 3.3.2 (for the first time) and I am trying to create the following table, so that the ID is the unique table/entity identifier and the sequence is a unique identity per group, in the table example clubs 123, 232 and 444 are groups.
ID Club Sequence
1 123 1
2 123 2
3 232 1
4 232 2
5 444 1
6 123 3
I want the Inserts to be just with the club category, both identities will be automatic.
Can this be done? And if so, how can I do it?
If the question is something like:
What is the nicest way to generate the values in the Sequence column according to this pattern?
Then here are some hints:
To have the DB generate the Sequence column, I think you need to look at a trigger-based solution. The "groups" of which you speak means nothing to the DB unless you create some (SQL-)code to handle it. You might want to create a stored procedure that generates the number and then an INSERT-trigger.
NHibernate can be told to ignore the column for INSERT and just read back the value generated by the DB. See Generated Properties in the NHibernate reference.
The structure you describe is similar to what a mapping of a list in NHibernate would generate, if you drop the Id column. A collection is always owned by something, so this could be a solution if you have a collection owned by Club. The Club column would then point to the collection owner, and the Sequence would be the index in the list. (I'm assuming the table is supposed to contain more columns, which would then be the elements in the list.)
I think what you need to do is think about what object model makes sense to you problem. Then design the tables to match that. NHibernate is good at handling existing legacy table models, but if you're starting from scratch it makes sense to create the model first.