Storing a collection in SQL

Question

[pre-able]
In my www.twipler.com project I want to allow people to link Twitter accounts. This will be achieved by having them login, selecting "link account" and then logging in again. This will effectively give me UserId-1 and UserId-2. Now I want to allow the user to login with either UserId-1 or UserId-2 credentials and then retreive the credentials for the other user. (Feel free to comment, but this is not the question)

[the setup]
C# 3.5, NHibernate, TweetSharp, SQL 2005.

[question]
How do I store an "bag" of unique integers in a database, such that querying one interger will return the other associated ones.

For example, I might create a table called UserLinks and have GroupId which I calculate before inserting a new link. It would look like this

UserLinks  GroupId   UserId
              1      12312784
              1      18329139
              2      19319332
              2      14529342
              2      21031023

But this feels a bit ugly and un-NHibernate. (Update: note there can more than 2 accounts)

There's mention below of finding a new GroupId using Max(GroupId)+1, but I realise since the UserId's are unique I can use the first inserted UserId of the group as the GroupId.

Answer 1

Actually that table looks to be about the right way of going to link (on the same level) 1..n records in a single table together.

Answer 2

Why would this be 'un-nhibernate' ? NHibernate is the bridge between the relational model of the DB and the OO model of your business classes.

Store the data in the DB in its most efficient / correct way, like you would do normally when using a relational DB. Create your business classes so that you'll be able to solve the problem. Use NHibernate to make the bridge between those 2 paradigms (relational data model and OO).

So: Create your DB - model in the relational way. Create your domain-classes: You could create a User class, which has a property 'LinkedAccounts'. This property can be a list of Account objects. In your NHibernate mapping, you can then specify where (from which tables) NHibernate should get its data from.

Answer 3

seems like you just need a typical self many to many relationship which you would implemtent as a table containing user_id_1 and user_id_2 as a composite PK, both of which are FK's to the userID of the user table.