November 20, 2008 at 9:57 am
Hello SQL Experts,
I have stumbled upon this site, so I joined because I know this is probably the place I can get my question answered. Ok, basically here is what I am trying to do. In Visual Studio 2005 using VB.Net and wiring up to a SQL Server 2000 database, I am creating a muliple page web form. So for each page (and understand, I'm not using the wizard or multi-page feature in ASP.Net 2.0 but actual pages. Which is about 8 ASP.Net pages and a SQL table for each page.) I am capturing a user's information when they come in to use our services. So, the first page is named demographics.aspx and captures things like First Name, Last Name, SSN, Date of Birth, stuff like that. And the next page (employment.aspx) captures their employment information and so on...Now, for each page, I have a SQL table named like Data_Demographics, and another table named Data_Employment, and so on...
Now, here is where this thing is getting tricky for me. With my VERY limited amount of knowledge of SQL, I would like to know what would be the BEST way to keep track of our users in each table while they are filling out our web form? So far, I have looked into identity columns and GUID's and that's not all; here's a little code that I came up with to create a userid for each user in VB.Net:
Private Function CreateUserID()
Dim str As New StringBuilder(Left(tbLastName.Text, 3))
Dim currentDate As Date = Now
Dim [ReturnValue] As String
str.Append(currentDate.ToString("MMMddyy"))
str.Append(Right(mstbSSN.Text, 4))
str.Append(currentDate.ToString("hhmmssff"))
[ReturnValue] = str.ToString.ToUpper
Return [ReturnValue]
End Function
As you can see, it's pretty unique in itself as it captures the first 3 letters of the user's last name, plus the last 4 numbers of their Social Security Number and when they click my button at the bottom of my web page it inserts it to the database right down to a fraction of a second along with the rest of their information. And I know it would be almost next to impossible for someone with the same last name, same last social be filling this thing out and click the button at the exact same instance. Besides, we don't have that many clients filling this thing out anyway. And the data is not going to stay in that database long before it's moved out anyway.
So, with all that said, I was wondering what YOU guys would do if you were building a multi table database to keep track of a user from table to table? I talked to my database guy the other day and he said I might just use SessionID from my ASP.Net web form, but then with a little more Googleing I found out that using SessionID is not a good idea.
And, I thought about passing the userid from the code above through the query string in encrypted format and dumping it into a hidden field on each page and that way it would be available and ready at each page to go into the database when the users move to the next page.
Is this a bad idea? Is there a better way? I know you guys do this stuff all the time. I hate to ask this, but, if you could point me in the right direction as to what kind of column I should set up (including correct properties), whether it should be identity, GUID, whatever, I would appreciate it VERY much. I am pulling my hair out... And I don't have much to pull out. (-:
Thanks SO MUCH in advance,
themillimeister
p.s. Sorry for the newbie questions, but, this whole development thing ... it's pretty new to me. 😀
December 1, 2008 at 9:47 am
I like identities, especially for something like this. A multi-page form, where you'll like to store the data after each page in case the session breaks and they need to reconnect. Identities make it easy.
If you have a heavy transaction load, GUIDs are nice because you can reduce a round trip. You can generate the GUId on the client and then insert it into the database. Since they're unique, you don't need to hit the db, generate a row, the return the ID to the client. Instead you can generate it on the client, insert it into the DB, not return anything and continue on.
If you have an AJAX type syste, this is nice.
Trying to generate your own unique key for people can be problematic if you're not 100% sure they will be unique. You have a good system, but have you thought about the fact the IDs can be for corporations or taxable entities and they might dupe the SSNs somehow? I'd be careful here.
December 2, 2008 at 8:58 am
Thanks for your reply Steve,
yeah I have thought about, what if they miss-type the last four of their social, or what if their session is broken. I know anything can happen. I do have validation set up so they HAVE to have all the text boxes filled before they can move to the next page, but, I know that when you're entering in numbers, such as a social, it's easy to transpose something. Then the horror would be going in there and fixing it for EVERY table for that one client before it's needs to be imported into our production database. I know that everything has to be right, or our, what we call our "intake" department, will be on the phone with them saying, "what's up with this bogus data?!" (-:
So, if you don't mind me asking, what would you do in this situation? Would you store session state in SQL Server? Right now I'm doing a search on that on Google. I've found a good article on that here:
December 2, 2008 at 9:51 am
The thing to do is have a separate PK, either identity or GUID, and then store the data, but allow for mistakes. Give them a confirm, maybe on the next page (you just entered xxx, now please fill out these fields).
I'd also be sure to allow changes later to the extent that it makes sense. People do make mistakes, and good sites allow corrections.
I wouldn't worry so much about session, because browsers crash, machines restart, etc. Store it, but have it in a changeable state, meaning that you allow changes to it in the app. You might decide to lock things at some point, in which case, perhaps you'd add a flag that marks a row as "locked".
December 2, 2008 at 11:18 am
Thanks a bunch Steve, that is some GOOD information. If you don't mind, here is my layout for the Data_Demographics table. Please keep in mind that I am not a SQL guy...and I'm pretty new to development as well, as this is my first major project. lol (-: Ohhh yes, I am a proverbial newbie.
CREATE TABLE [Data_Demographics] (
[RowID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [nvarchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Middle] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Suffix] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MaidenName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateOfBirth] [smalldatetime] NULL ,
[SSN] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CountryOfCitizenship] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Gender] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FinancialInstitution] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USCitizen] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreationDate] [datetime] NULL CONSTRAINT [DF_Data_Demographics_CreationDate] DEFAULT (getdate()),
[NPNNumber] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,--National Producer number of insurance agents if they have one.
CONSTRAINT [PK_Data_Demographics] PRIMARY KEY CLUSTERED
(
[RowID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Sorry about all this extra stuff, I just copied the whole table into Notepad++. So, should I maybe change the UserID column to an Identity column? Steve, if you could just give this a quick look-over I would appreciate it very much.
Thank you very much, milli
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply