Database to hold scripts

[mdlister]

Morning,

Novice when it comes to tables and designing them, and am looking at a way to create a database that will store all the scripts that i create to help me do things as well as maybe keep version control so that if i modify it i can see the modifications and role back if things go bad, i would like to use a web front end to view the data and maybe post back as well tho at this moment in time i am unsure if that is possible.

I am using acces 2007 and have downloaded the new odbc settings from microsoft and installed on my iis box so i can use the new file extentions.

My tables so far are as follows:

tblScript
ScriptID (PK)
ScriptName
ScriptDescription
DateCreated
Notes
ExecutionMethod
ScriptImpact

TblLines
LineID (PK)
ScriptID (FK)
LineNumber
Line
Notes
DateAdded
DateModified
TabCol
NewLine

and so far i have entered one script and if i view the information i can see that it links sucessfully to all the lines of code that go with the scriptid.

as i have scripts that go over 1000's of lines is there a way to automate the import? Maybe paste the script to a memo field and use vb to read each line and add it to the database? would i do this on the database or asp?

also how about version control would i use another table for this or could i have another field for active/inactive and let the asp check for the modified date? but then if its modified i'd still want to keep track of the modification?

Any help would be great, i'm off till monday for christmas but will be back online again then

until then

Merry Christmas

Mike

 

[The_Doc_Man]

I would wonder about the normalization of the approach you are using, but let's get a couple of items out of the way first.

How to import? I see a few ways to do it. The way I would do this personally is to have a form that allows me to select or enter the basic descriptive information, then have a control button to trigger the import proces. Have it execute some code via the OnClick event routine.

First, get the OnClick routine to trigger a Common Dialog Box to do an Open File function. You can search this forum for posts on the topic of the Common Dialog box. Pat Hartman posted a nice article but it was a while ago. So you might have to expand the time limits of the search.

OK, so now you have a file name. Open the recordset for your Lines table. Open the file name for input. Now loop through the file using the VBA InputLine verb to capture one line of the file. Using .AddNew on the recordset, store the line along with the data entered earlier on the form from before you clicked the Import button. Trap errors in the loop so that when you hit the end of the input file, you can close the file and close the recordset.

OK, let's talke about table structure.

I would have your Script "header" table that is mostly descriptive. That's more or less OK, no real quibbles off the top of my head.

The Lines table, however, stores too much data. I might have an intermediate "versions" table that would include the dates and such, plus a master version index that is perhaps a PK via autonumber. Link the Script ID to this, but then have a lines table that contained only:

Lines: VersionID, ScriptID, LineID, TheLine

where I compute (not autonumber) the LineID. Since I would be in VBA, it would not be an AutoNumber in the Access sense, it would be a line counter that is maintained within VBA code inside the import loop. Version ID and Script ID would be foreign keys, and to be honest, Script ID is a little redundant in this table. I might see it as a useful thing, but maybe not. (But then again, I'm not a purist in all matters.)

Anyway, when I was done, I would update the Version table to show the number of lines stored for that given script version.

 

[mdlister]

Thanks Doc Man,

i understand most of what you are saying, i am trying to get the database structure as you suggested but am a little confussed with some of the things you suggested?

i have modified tblLines and created a tblVersions and left tblscript as it is.

tblLines
LineID (PK not autonumber)
ScriptID
VersionID
strLine

tblVersion
MstVersionID (PK autonumber)
ScriptID (FK tblScript.ScriptID)
LineID
Notes
DateAdded
DateModified
TabCol
Newline

is this correct? I'm having a little trouble getting my head round the tables and relationships?

 

[mdlister]

ok so i was a little confussed and resorted to the classic pen and paper to draw me out the diagram of what the tables would look like and put some data in to see what would happen and i think i have it

tblScript
ScriptID(PK)
VersionID(FK tblVersion.VersionID)
Date Created
Description

tblVersion
VersionID(PK)
LineID
DateModified
Notes

tblLines
LineID(PK) <- Auto number just to number the fields as line number will be duplicated so cant have that as PK?
VersionID
ScriptID
LineNumber <- Generated by vba as each line is read in
Code
Comments
NewLines
Tabs

I think i am getting it looking at it more, tho the PK for tblLines cant be the line number as that would be duplicated everytime there is a new version?

If i am seeing this correctly it will first import the script in to tblscript as scriptid 1 version 1 and all the lines of code are in tbllines but this last bit gets me muddled up as how does the lineId match up?

i have attached an example if this helps what i am having trouble understanding.

Thanks again