Injured table
InjuredID autoNumber primary key int,
passenger1 varchar(50),
age int,
streetAddress varchar(50),
city varchar(50),
state varchar(50),
zip varchar(50),
passenger2 varchar(50),
age int,
streetAddress varchar(50),
city varchar(50),
state varchar(50),
zip varchar(50),
passenger3 varchar(50),
age int,
streetAddress varchar(50),
city varchar(50),
state varchar(50),
zip varchar(50),
passenger4 varchar(50),
age int,
streetAddress varchar(50),
city varchar(50),
state varchar(50),
zip varchar(50),
passenger5 varchar(50),
age int,
streetAddress varchar(50),
city varchar(50),
state varchar(50),
zip varchar(50)
I am of course I have more tables like vehicle but what I am having problem with are these 3.
We are converting manual forms into electric forms.
Currently, using the manual form, when an accident occurs, a form is filled by completing information about the driver, and slots filled for up five passengers in the car.
So you a scenario like:
DriverID,
age,
streetAddress ,
city,
state,
zip,
passenger1,
age,
streetAddress ,
city,
state,
zip,
passenger2
age,
streetAddress ,
city,
state,
zip,
passenger3
age,
streetAddress ,
city,
state,
zip,
passenger4
age,
streetAddress ,
city,
state,
zip,
passenger5
age,
streetAddress ,
city,
state,
zip
What is the best way to handle this situation using asp/form?
Thank you in advance
hi,
first rule of normalization, remove any repeating fields and create a new relation/table for each set of data.
age, city, state, zip are repeating fields. Street Address can possible be normalized further by splitting houseNumber and StreetAddress and then making a relation/table for streetaddress.
I would certainly create a new relation/table for age, city, state and zip.
Sorry to take you away from your original question, personally i feel under your present database structure you are going to find it very hard to write any code. Once you have you're database fix i am sure one of us will be able to give you some code to get you started.
hi Dave and ]SWR] Ribeyed!
Thanks to both of you for your input.
Now I can understand normalizing the Injured table further, for instance, adding an InjuredType such as
InjuredType
InjuredTypeID
InjuredTypeName
then have InjuredTypeID in the Injured table.
But don't you think it is a bit overnormalizing when you break them up like these:
Age Table
AgeID autoNumber primary key int,
Age int,
City Table
CityID autoNumber primary key int,
City varchar(50),
StateID foreign key int,
State Table
StateID autoNumber primary key int,
State varchar(50),
Zip Table
ZipID autoNumber primary key int,
ZipCode varchar(8),
CityID foreign key int
hi,
lol, "over normalized" normalization is a technique. When we design a database for a relational system, the main objective in developing a logical data model is to create an accurate representation of the data, its relationships, and constraints. To achieve this objective, we must identify a suitable set of relations. A technique that we use to help identify such relations is called normalization.
A major aim of relational database design is to group attributes into relations so as to minimize data redundancy and thereby reduce the file storage space required by the implemented base relations.
Relations that have redundant data may have problems called update anomalies, which are classified as insertion, deletion or modification anomalies.
The database structure you posted would have insertion anomalies to start with. You can insert into the injured table a record about an injury without any relating record in the Driver table. This is bad.
Your database would also have deletion anomalies. If you deleted a record from the driver table you would have a record left in the otherdriver table, this would cause problems.
And again your database would have modification anomalies. If driver 1 changed his details and he was in an accident if you changed his details in the driver table you would be left with the old details in the injured table.
You would have more problems when it comes to inserting into the database with your injured table. You have 4 age, streetaddress, state, city etc. in the one table, there are many reasons why this is not done, one would be; it would not know which one you want to insert data into. Say I said
state = "Mississippi"
Then if you inserted this into the database it would know which state field to write to.
The driver and otherdriver tables are not normalized you should have 1 table for all drivers and another table that will hold information about which driver, first or second, they are.
so something like this would be normalized:
Impressive lesson on rules of normalization.
I thought I know it atleast to the 3rd normal form.
F.C Codd must be turning in his sleep now.
Anyway taking further, here is my full design in relation to the accident project.
Damage table (this is damage to car and has prepoluated data from police dept)
damageId int primary,
DamageDesc varchar(1000)
eg damageId DamageDesc
1 total damage
Collision table (two cars involbed also prepopulated from police dept)
collisionID int primary key,
CollisionDesc varchar(1000)
eg
collisionID collisionDesc
1 bumper to bumper
Age Table
AgeID autoNumber primary key int,
Age int
City Table
CityID autoNumber primary key int,
City varchar(50),
StateID foreign key int
State Table
StateID autoNumber primary key int,
State varchar(50)
Zip Table
ZipID autoNumber primary key int,
ZipCode varchar(8),
CityID foreign key int
DriverConditon table (also prepopulated and taken from police dept. This determines if the driver is under influence of alcohol or something.
conditionID in primary key,
conditionDesc varchar(1000)
Notice that all those with prepopulated data do not have autoNumbers.
Police table
policeBadgeId int primary key autonumber
officersName varchar(50),
dept varchar(50),
caseNumber int,
accidentEventID int (from accidentEvent table)
factors table (contributing factors to the accident, again prepoluated and taken from the police dept)
factorsID int primary key,
factorsDesc varchar(1000),
eg,
factorsID factorsDesc
1 failed to yield
insurance table
insuranceId autonumber int primary key,
insuranceCo varchar(50),
accidentEventID int (from accidentEvent table)
workmansComp table
compID int primary key autoNumber,
name varchar(50),
dept varchar(50),
agent varchar(50),
accidentEventID int (from accidentEvent table)
Location table (location of accident)
locationID int primary key autonumber,
locationDesc varchar(100),
accidentEventID int (from accidentEvent table)
vehicle table
vehicleID int primary key autoNumber,
LicenceNumber varchar(1000),
make varchar(50),
Model varchar(50),
vehicleYr varchar(50),
accidentEventID int (from accidentEvent table)
weather table (also prepopulated and taken from police dept)
weatherID int primary key,
weatherDesc varchar(1000)
eg:
weatherID weatherDesc
1 clear
The main table
AccidentEvent table
accidentEventID int autoNumber, primary Key,
condID int (from driverCondition table),
factorsID int (from factorstable),
compID int (from workmansComp table),
collisionID (from Collision table),
damageId (from damage table),
weatherId (from weather table),
comments varchar(2000),
drugtest varchar(50),
result varchar(1000),
atfault varchar(50)
This is basically what I have as far as design is concerned.
I know this is a lot of information to digest but take a look and see if there are any additonal design changes to be made.
Thanks to you folks again
<body>
<form name="Insert" method="post" action="">
<p>Event location:</p>
<p>
<input name="txtEventLocation" type="text" maxlength="50" value="<%request.form("txtEventLocation")%>">
</p>
<p>Event description:</p>
<p>
<textarea name="txtEventDescr" cols="40" rows="7" value="<%request.form("txtEventDescr")%>"></textarea>
</p>
<p>Involved persons:</p>
<table width="57%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>Name</td>
<td>Age</td>
<td>Location</td>
<td>Type</td>
</tr>
<%response.write(strGrid)%>
</table>
</form>
</body>
</html>
<%
End Function
'-----------------------------------------------------------------------------------
'
'-----------------------------------------------------------------------------------
Function BuildPersonTypeMenu(intSelection, intCount)
'-----------------------------------------------------
'build ADODB.Connection here, should be called "objConn"
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "" 'accident'
'-----------------------------------------------------
'retrieve all availble Injured types from database
'--------------------------------------
strSQL = "SELECT * FROM injuredType"
set rsData = Server.CreateObject("ADODB.Recordset")
rsData.Open strSQL, objConn
'--------------------------------------
'start building the menu as strMenu. intCount is used to keep the menu
'value unique in the grid:
strMenu = "<select name=""lstinjuredTypeMenu"""&intCount&">"& vbNewLine
Do While Not rsData.EOF
strMenu = strMenu &"<option value="
strMenu = strMenu & rsData("TypeID")
'check if there was selected a value before. If so, make sure it's displayed
'as selected, so the user does not have to re-select that value, every time a
'line is added to the grid
If intSelection <> 0 and CInt(rsData("TypeID")) = Cint(intSelection) then
strMenu = strMenu & " selected>"
else
strMenu = strMenu & ">"
end if
strMenu = strMenu & rsData("TypeName")
strMenu = strMenu & "</option>"&vbNewLine
rsData.MoveNext
loop
strMenu = strMenu & "</select>"& vbNewLine
'kill the recordset
rsData.close
set rsData = nothing
'kill the connection
objConn.close
set objConn = nothing
'asign the menu to return value function
BuildInjuredTypeMenu = strMenu
End Function
'-----------------------------------------------------------------------------------
'
'-----------------------------------------------------------------------------------
Function Build_PersonGrid(strGrid)
Dim intCount, x
If request.form("Count") = "" then
'set default number of lines in the grid, in this case 5(!)
intCount = 4
else
'retrieve current amount of lines in the grid, increment by 1
intCount = Cint(request.form("Count") + 1)
end if
Build_InjuredGrid = strGrid
End Function
'-----------------------------------------------------------------------------------
'
'-----------------------------------------------------------------------------------
Sub Process_InsertEvent
Dim strSQL, intCount, x
intCount = request.form("Count")
'-----------------------------------------------------
'build ADODB.Connection here, should be called "objConn"
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "" 'accident'
'-----------------------------------------------------
response.write("Action was a succes. All data was processed in database")
End Sub
'-----------------------------------------------------------------------------------
'
'-----------------------------------------------------------------------------------
Sub Main
Dim intAction
If request.form("cmd_AddNewLine") <> "" then
intAction = Cint(0)
elseif request.form("cmd_Process") <> "" then
intAction = Cint(1)
Else
intAction = Cint(0)
end if
Select Case intAction
Case 0
strGrid = Build_injuredGrid(strTemp)
Display_EventInsert(strGrid)
Case 1
Process_InsertEvent
End Select
End Sub
'-----------------------------------------------------------------------------------
'
'-----------------------------------------------------------------------------------
Call Main()
%>
hi,
looking at your code i see you haven't got much of a form for taking the input for all your tables from the user. If you can build the form with all the textboxes/textareas that you need to insert into the database the we can help you with your insert statements. At the moment you have a form with only 1 textbox and 1 textarea for around 6 tables and 30 fields. So spend a few days preparing the form and i will help you process it.
Hi [SWR]Ribeyed
Thanks for attempting to help me with this.
This is really appreciated.
A few things to make you aware of.
There are two forms. First called accidentReport.asp. This is carried over to the next form called propertyDamage.asp. The way the forms are designed is exactly the way the client wants it to look like.
They want to be able to enter data on the first form and click continue and complete data entry on the next form before submitting.
Once they hit the submit button, the other asp page that actually does the insert will be invoked.
If you find it necessary to make any changes, please do so.
I will probably post the forms seperately due to space limitations on the forum.
Again, thanks a bunch.
*******************
This is accidentReport.asp
***************************
hi,
ok you say you have 2 pages but you posted code for propertyDamage.asp, accidentReport.asp and you provided a zip file with a page called accidentTrac.asp, which pages am i working with?
sorry!
I posted propertyDamage and when I attempted to post accidentReport page, I realized I was making the forum look vey untidy because the pages were long.
So I zipped them up.
If you don't mind, please ignore everything else I had posted and just grad the zipped file.
It contains both pages.
Thanks a lot for the help!
hi!
We have sql db but I would have no problems converting from access.
Please whatever db you are comfortable with.
thank you (can I just call you Ribeyed?)
Reply With Quote
Bookmarks