Form Field & SQL questions

[dirt29]

I am preparing to try and write an application that will be using quite a few form fields to store user data. And I wanted to ask a few questions that I'm unsure about. The layout of the project will incorporate many pages in which the user will input data related to themselves. So my questions are:

1) In my table setup for my DB(which is SQLServer) I want to use several tables, each that store diffrent sections of the data. I was thinking that using the sessionID might be useful as the PK and also for referencing the records. Any thoughts there?

2) On my forms, I have a massive amount of fields. How do you go about setting it so that I can make it loop through all the fields on a particular page and only write the ones that are not Null? I.E. Some will be required fields which must be filled in, others will be optional.

I'm thinking of setting up my tables like this:

sessionid   varchar(50)   NOT NULL
fieldname  varchar(50)
fieldvalue  varchar(50)
inputtime  timestamp(8)

I was thinking that when the user submits thier form, that I could use a check by a SELECT statement to see if thier SessionID was in the DB, if not then I could use a INSERT to input thier data, otherwise I could simply use an UPDATE to alter whats thier, in case they make a mistake and need to go back.

I am somewhat new at this, and am more or less looking for some feedback on how I might accomplish these tasks, as well as any other recommendation more experience developers may have.

Thanks in advance.

[Ribeyed]

Hi,
answer to question 1 is yes, SessionID would be the Primary Key however you can set the datatype to auto that way every sessionID would be unique.

question 2 you can loop through your form elements using this:

for each item in requset.form
  'do something here.
  response.write "the item is: " & item
next

or if you know the elements name then all you do is an if statement.

if txtbox1 <> "" then
 response.write "insert into database"
end if

hope this helps

[russell_g_1]

are you talking about using the asp sessionId in your database?

i would tend to avoid this unless you're only dealing with temporary data that doesn't matter once the session expires.

in your case i would probably create a table like this

create table person 
(
id numeric(18,0) identity(1,1) not null,
inserteddate datetime not null default(getdate()),
deleted int not null default(0)
and any other fields you might want (maybe a latest sessionid if you really want it?)
)

and then the other table of possible fields like this

create table personfield
(
id numeric(18,0) identity(1,1) not null,
name nvarchar(100) not null,
deleted int not null default(0),
inserteddate datetime not null default(getdate()),
)

and then a final table linking the people and the fields

create table linkpersonfieldperson
(
id numeric(18,0) identity(1,1) not null,
personfieldid numeric(18,0) not null,
personid numeric(18,0) not null,
value nvarchar(1000) not null,
deleted int not null default(0),
inserteddate datetime not null default(getdate()),
)

so when you save someone you add a new person record and use its id when you add all the field data.

does that make sense?

it might be easier to write the form out as well if you've got the fields all in a table like this.

this is almost exactly the same as this thread http://www.webdeveloper.com/forum/sh...d.php?t=120049

[dirt29]

Yes thank you very much, that makes sense.

One followup question, in your table structure, you have a delete int column. How are you using that?

And also, as I move from form to form how do I go about updating information in each table? The one reason I thought I'd use the sessionID was because I figured it might be good to use that aas a check to see if they have already entered data or needed to update it. I.e. If they were to hit back button and re-enter.

So I guess what I'm asking is, am I establishing the info in the person table on the intial insert of data and how am I linking that person as they go from form to form? Sorry if these are stupid questions, just want to make sure I'm following your logic here.

[russell_g_1]

i use the deleted columns to logically delete data from the database. so all the queries run against the db have to say "where deleted = 0" somewhere in them. the advantage of this is that you can restore the data very easily if it gets deleted accidentally. this obviously doesn't replace db backups or triggers creating complete audit trails, but it is useful for quick reference and restoring.

anyway, enough of that.

your database structure should essentially be unrelated to the application(s) that access it. it should just be able to store the data you need persisted in a normalised structure. obviously there are exceptions to this where it makes sense to do things based on the way the data is being handled outside the system.

you don't really need to use the sessionid for checking what's been entered if you substitute it with something else (a person id). when a user submits the first data about a new person, you simply need to create the person record and get its id back, put that person id in session for later, then when you go to save each bit of person related data you just use the person id in session. this would let you enter more than one person per user session as well.

to control the effects of users pressing back buttons and refresh buttons you need to use something like a pagestamp. if you were to put a value in session on each request and place it into the html page (in forms and anchors), you can then check if they match on the next request, meaning that the user has gone from the page before to the next page without doing anything else.