super-type and sub-type tables

[SunTop]

Hello everyone

I have a table in my database that records the work done, this work could be either a repair of an accident or new work. So what I did is designated the work table as a super-type and two other tables (accident and NewWork) as sub-types as follows:

tbl_Work


pk_WorkID
fk_StateID
fk_VillageID
fk_StreetID
Location
Deleted


tbl_Accident
pk_WorkID
AccidentNumber
fk_SupervisorID
fk_StatusID



tbl_NewWork
pk_WorkID
NewWorkNumber
fk_StatusID



I connected the super-type table with the other two sup-types tables using the pk_WorkID field as a primary key in both the super and sub tables as one to one relationship, what I want to do is force the user to full all the fields in the super and sub table before allowing him to commit the record, I tried doing that by changing the required filed property to yes in all the fields, but what happened is that if the user stops exactly after he fills the last field of the super type table the entries gets committed to the table but without a corresponding record in either of the sub type tables and this creates a data integrity issue in my database.

how can I force the user to full both records in the super type and sub type tables before the entries gets committed to the table ?

[ranman256]

why wouldnt you just have them both in 1 sub table,
and give a flag for "accident", or "work"

[SunTop]

ranman256,

accident requires information that is different then what a work does, for example the accident has a supervisor field that does not exits in work

[orange]

Is that the only difference?
Can you tell us a little more about the "business" involved, and the volume of records/activity?

[SunTop]

orange,

their are some a few other differences:

- an accident must have a sequential id number and a new work does not require a sequential id number
- status for accident is different then status for work, accident status are (not checked, repaired, not repaired), status for new work are (repaired, not repaired)

am creating a database for a company that maintain roads, some time the maintenance is done to repair a traffic accident, sometimes it is to add something to the road. So what happens is that a supervisor goes to the field to evaluate the damages of a traffic accident and then he records that in work table and the accident table, after a number of accidents have been recorded then a work order is issued to fix all these accidents, the work order table has many to one relation with the accident table, so that means the work order contains a number of accidents. Some times the client ask to do some maintenance to road that is not a result of a traffic accident, so this new work is recorded in the work table and the new work table and then a work order is issued for this new work in the work order table, the volume of of accidents and new work is very minimal (about a dozen each month)

[orange]

Just a few comments on your description:

an accident must have a sequential id number I think the must here is a wish and not a requirement, but you know your situation better than readers. It seems to me (from experience) you want each accident to have a unique number/identifier. You can use a query to get a count of "things" based on some criteria. It would also seem that Status might include Planned, InProgress, Completed, Canceled... but again you know the work and readers don't.

It seems a WorkOrder is for "road work". And Road Work may be initiated from "traffic accident" or "other" (representing new work including some list of known things -pot holes, resurfacing, widening,.....painting lines...)

I'm not trying to add work to your project, just making a few observations to ensure you are looking at the big picture. Sometimes if you step back, you can abstract things into groups/patterns and simplify your effort. May not be the case here, but discard things from a point of knowledge. Too many develop a partial solution and don't spend enough time in analysis, design and feedback before jumping into a physical database solution.

You may find this link helpful.

Good luck with your project.