Hi, I need to create a simple web form that a person fills out, submits, then gets checked by another, then a third and finally the instructions in the form get get acted on by the third, then it is distributed via email and available in a web interface.Pretty much can handle that part but the web form also needs an uploaded attachment that will require versioning as well. CREATE TABLE forms ( formid int IDENTITY (1, 1) NOT NULL , sono varchar (6) NOT NULL , filenames varchar (2000) NULL , desciption varchar (2000) NULL , notes varchar (2000) NULL , needsinchconv bit NOT NULL CONSTRAINT DF__forms__needsinch__3F466844 DEFAULT (0), doneinchconv bit NOT NULL CONSTRAINT DF__forms__doneinchc__403A8C7D DEFAULT (0), updatedjobdoc bit NOT NULL CONSTRAINT DF__forms__updatedjo__412EB0B6 DEFAULT (0), updatedsurflog bit NOT NULL CONSTRAINT DF__forms__updatedsu__4222D4EF DEFAULT (0), filename1 varchar (128) NULL , CONSTRAINT PK__forms PRIMARY KEY CLUSTERED ( formid ))
CREATE TABLE formstatus ( statusid char (10) NOT NULL , formid int NOT NULL , userstep1 char (10) NULL , step1done bit NOT NULL CONSTRAINT DF_formstatus_userstep1done DEFAULT (0), step1date datetime NULL , userstep2 char (10) NULL , step2done bit NOT NULL CONSTRAINT DF_formstatus_userstep2done DEFAULT (0), step2date datetime NULL , userstep3 char (10) NULL , step3done bit NOT NULL CONSTRAINT DF_formstatus_userstep3done DEFAULT (0), step3date datetime NULL , CONSTRAINT PK_formstatus PRIMARY KEY CLUSTERED ( statusid ), CONSTRAINT FK_formstatus_forms FOREIGN KEY ( formid ) REFERENCES forms ( formid ))
stepdone"x" represent the release state of the document or position in the queue.So a typical insert might look like this.... INSERT INTO forms(sono, filenames, desciption, notes, needsinchconv, doneinchconv, updatedjobdoc, updatedsurflog, filename1)VALUES('c3500', 'c3500q-1.igs;c3500q-2.igs;c3500q-1.prt;c3500q-2.prt', 'please note insert q3 and q4 are mirrored',' see above', 1, 0, 0, 1, 'c3500q'+CAST(@@identity as varchar(24))+'.zip')see the last value for filename1 that is what I was going to use to rename the uploaded file,or would a timestamp be better. To me it dosen't seem so.because the form data contained in the forms table isn't time dependent until it is submited with authorization. Each unique form has a unique package. If things change before the journey through the queue then ressubmit the .zip upon editing the original form and it will get overwritten. ie. the original would be voided. No need to maintain the original submission.Further can someone see the avoidence of use of an identity column in this scenerio. Or any other suggestions, this application has probably been written a few thousand times. Guess it's my turn. 
Voted best SQL forum nickname...."Tutorial-D"Edited by - sitka on 04/21/2003 08:33:57
lightweight document versioning
Posted - 2003-04-21 : 08:31:15