I am going to use forms authentication but I want to be able to link the asp.net users to some tables in the db for example
If I have a class and students (as roles) I'll have a class students table.
I'm planning to put in a Users table containing a simple int userid and ASP.NET username in there and put userid wherever I want to link the users.
Does that sound good? any other options of modeling this? it does sound a bit convoluted?
If you're going to use ASP.NET membership, you should create the users with the ASP.Net Membership classes and database tables. This will create a user in the dbo.aspnet_Membership with the appropriate roles set for that user, upon successful insertion into this table, you can get the UserId from the dbo.aspnet_Membership table.
In YOUR Users table create a table with an int(auto-incrementing) ID, and also create a membershipID field in your table(rather than username) of type uniqueidentifier called say, membershipID. This is where you would store the value from the aspnet_Membership table to link the two together. You should also put a foreign key relationship on your membershipID field for referential integrity.
EDIT: Just to clarify with the other posts, id is of type GUID in your ASP.NET code, and of type uniqueidentifier in your SQLServer database.
Basically, the ASP.NET membership service creates a GUID per User, so if you need to have a foreign key of the user in a table, then this is what you should use. As such, you will not need the mapping table of a username to a userid as you already have a unique identifier per user (the aforementioned GUID)
If your looking to extend the amount of information one can store against a user, then this link has all the details regarding that.
We did this for a system where we retro-fitted forms authentication into a system using another authentication system. We added a column to our existing user account table. We added a GUID column, since this is what the aspnet_xxxx tables use, and filled it with the GUID from the aspnet tables. We then wrapped creation of our entry in our table into a transaction dependant upon the success of account creation using the asp.net membership calls. It's very important to make sure you sync the tables together with a transaction or something to make you don't have entries in one system and not in the other.
I'm not 100% sure, but I think that usernames can change so you shouldn't use that to link your systems. Use the GUID.
Hope this helps