April 6, 2003 at 10:01 am
I need a very serious help.
I have a total of 25 tables.
Several of these tables are lookup tables and most of these lookup tables have prepopulated data.
The lookup tables are:
drivercondition with prepopulated data,
damage with prepopulated data,
collision with prepopulated data,
dept with prepopulated data,
weather with prepopulated data,
age with prepopulated data (age is in range from 18 to 80)
factors with prepopulated data,
Injurytype with prepopulated data,
vehicleType, - whether company vehicle or non company vehicle
driverType, - whether driver is a company employee or non company employee
PassengerType. - whether passenger is on a company vehicle or non company vehicle
The rest of the tables are:
vehicle, - vehicle involved in a car
driver, - driver of a car
police, - police who response to accident
insurance, - insurance company
witnesses, - witnesses to an accident
location, - location
city, - city
state, - state
zip, - zip code
passengers, - passengers
passengerInjured, - injured passenger
DriverInjured,-injured driver
workmansComp - workmans compensation
AccidentEvent - main table.
A brief history of what is going on.
A company allows its employees to drive company vehicles to work and to run other errants.
A company vehicle can be driven by more than one employee, just as one employee can drive more than one vehicle.
There have been a good number of accidents involving company vehicles and and non company vehicles and also company vehicles and company vehicles.
The company has therefore decided to track these accidents.
When an accident occurs, the company wants to know what date and time the accident occured (accidentDate field on accidentEvent table); who is driving the company car(driver and drivertype); is the accident between company car and another company car or between company car and non company car(vehicle and vehicleType tables); what is the weather condition(weather table);
What type of collision(collision table);
what type of injuries are sustained(injuryType table);
what type of damage to vehicles(damage table);
What is driver's condition(drivercondition table);
what factors contribute to the accident(factors table);
Are there passengers in each vehicle(passengers and passengerType table). Passengers could be between 0 and 5;
Are there witnesses (witness table). there could be 0 to n witnesses;
Who is the police on the scene. One accident could attended by one or more Police,just as one police could be in one or more accident scenes just as on (Police and accidentEvent tables);
What are the ages of the drivers and passengers (age tablee);
what are there addresses; what are there cities(city table);
what are there states(state);
what are their zip codes(zip table);
what is their insurance company(insuranceCo);
What is the location of the accident(location);
Are there any workmans Compensation for company employees (workmansComp);
Then there is the accidentEvent table.
My problems so far as you can see is knowing how to code this stored procedure.
i have designed the tables and the tables appear fully normalized.
I have also designed to front end interface (screen).
How do I code the stored procedure to capture which is company driver and non driver company.
Same question applies to vehicle.
How do I code the sp determine that how many passengers are involved in an accident?
which one of the pasengers are company and which are not;
which are injured and which are not; we cannot have more injured passengers than number of passengers.
I am prepared to send the entire table structure, the diagram and the interface that clearly visually explains what is going on.
I need your help, please.
I have been looking at this now for more than a week and don't know how to get started.
Please help me. This is complicated and this site has a reputation for having the technical expertise to help.
Please help me.
I will supply whatever additional info is needed.
Thank you very much in advance.
April 6, 2003 at 11:16 am
This seems like quite a complex question. It seems like you need someone to program your middle tier and I believe it could take some time to do this. It would be a bit difficult to solve everything in the Forum...
Do you want everything to be solved on the DB level?
April 6, 2003 at 5:48 pm
hi!
When you say middle tier, do mean asp, coldfusion etc.
If this is what you mean, I do know asp and most the work I do, I do with asp but this is a bit too complex for my knowledge of asp.
Secondly, for efficiency case, it is better to use stored procedure.
Again, I have used stored procs extensively but this is very complex for me.
So to answer the rest of your question, I have done the database design which is fully normalized.
I have used asp to code the interface that captures the essence of what I need as far as making it easy to tell, in case of an accident, which is driver is an employee driver, which is not; how many passengers are involved in an accident and how many of them are injured; of the passengers, how many are employee passengers and how many are not; if there are any witnesses and if yes, obtain information about them; how many police are on scene and their information; whether the vehicles involved in an accident are all company vehicles or whether only one is.
I have captured these information on a form.
I have been working on the page that will actually store these records in our database.
So far in the page that I having been working on to store record in the db, I have captured police info, age info, witnesses info, city info, zip info, and state info. All of these are now inserting records into the db using asp.
The areas I am having problems with are the drivers, vehicles, and passengers.
Basically, here, I need to determine the drivers involved in accident, which is company driver, which is not, or are they both company drivers; is any of them injured?
If yes, obtain their info.
What about passengers, are there any injuries?
If yes, how many and obtain info about them.
Then obtain info about the vehicles they are driving.
So yes this is extremely complicated but I have done a lot of the work.
April 6, 2003 at 6:24 pm
I don't know a lot about ASP, but why not break the process down into smaller chunks, with one form per entity (driver, passenger, vehicle, etc.) For example, you've already determined the number of passengers, so bring up a form for each one to gather relevant data (in a loop?), and use a corresponding sp to store that entity. Then do the same for the other entity types. This keeps the code and logic a lot simpler since you're only concerned with one entity at a time. This is how I (and most other programmers) deal with this kind of scenario.
Of course, maybe I just missed your point altogether. If so, please explain.
Jay Madren
Jay Madren
April 6, 2003 at 6:49 pm
Just off the cuff, if you can ship the parameters to the middle tier (we use VB6 dlls) and then apply the business rules, calling different sp's to populate the tables.
As a previous poster suggested then one can write the rules / steps down in more discrete steps.
Step 1 Populate drivers info with a sp via ADO
Step 2 Populate passenger1 info with a sp
etc via ADO.
If any fail / broke the rules then rollback.
April 8, 2003 at 5:56 am
hi everyone!
I am having some success doing this with asp right now.
Thanks for everyone's help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply