Table Structure Help

[Ron A.]

I am needing some assistance in creating a solid table structure in creating
a database that stores vehicle permenant waiver items (items that will not be
repaired) and produce a report showing waivers for a specific vehicle or
organization. Specifics are as follows:

- 1 organization has a master Org Code
- an organization can have many sub Org Codes (multiple shops in 1 org)
- an organization can have many Vehicle Control Officers (usually 1 per sub
Org Code)
- Vehicles are assigned to master org code and sub org codes
- One vehicle can have many waivered items

I hope I have explained this well enough.

Thanks,
Ron A.

 

[Allen Browne]

In the table of organisations, you will need fields like these:
- OrgID AutoNumber primary key
- ParentOrgID Number blank if this isn't a member of another org
- OrgName Text

Will there ever be a case where vehicles could be reassigned? If so, you
will need a table identifying what vehicle was assigned to what org and
when:
- VehicleID which vehicle was assigned
- OrgID who the vehicle was assigned to
- AssignDate when this vehicle was assigned to this org.
All 3 of those fields required, and a unique index on the combination of
VehicleID + AssignDate, so you can tell who it's currently assigned to.

Not sure how you do your waivered items, but it may be that the waivered
items depend on the vehicle type. a) If you had 15 instances of the same
kind of vehicle, would they all have the same waivered items? b) Or might
they be really individual (e.g. some have towbars or extras and you need to
handle those)?

If (a), you need a VehicleType table, linked one-to-many to the waivered
items. If (b), I suggest you still have a VehicleType table and a table of
default waivered items for that table, but the actual waivered items will
relate to the specific vehicle.

 

[Dennis]

Ron,

Maybe other people can figure out your data, but I don't belive that you
have provided enough detail. I think you need to provide some additional
backgroup detail.

Let's starts with a couple of questions.

1. Can there be multiple organizations or is there just one.
2. Can there be multiple Vehicle control officers per shop / sub Org Code.
3. Is a Vehicle Control Officer assign to a specific shop or do they work
out of any shop?
4. Why is a vehicle assigned both a master org code and a sub org code. In
this structure, you could assing the vehicle to a master org code that has
nothing to do with the sub org code. If you assign a sub Org Code to a shop,
and you assign a shop to an organization / master Org. When you assign a
vehicle to a sub Org Code, that automatically assigns that vehilcle to a
master org code. By having both fields, you are implying that you can assign
the vehicle to one master org code and to a totally different sub Org Code.
5. You don't tell us how you identify an organization, shop, vehicle
control officer, vehicle.
6. What is an Org Code, what is a Sub Ord Code? Is that what identifies an
organiztion / shop or is that just an attribute of the organization / shop?
7. Are the Vehicle Control Officer associated with a vehicle?

It seems that there are a lot of missing details!

tblOrganization
Key: Master Org Code
Data Org Name

tblShop
key: Sub Org Code
Data Master Org Code fk to tblOrganization
Shop Name

tblVehicleCtlOfficer
key: OfficerNo - automatically assigned system number.
Data: Sub Org Code

tblVehicle
key VehicleNo - automatically assigned system number.
Data Sub Org Code - This is the foreign key to tblShop.
This associates the vehicles with the
shop and
therefore the master organizations.
OfficerNo

tblWaiver
key WaiverNo - automatically assigned system number.
Data VehicleNo foreign key to the tblVehicle table.



Good luck

Dennis

 

[Ron A.]

Thanks Allen and Dennis. I know I didn't go into the greatest of detail, but
you have given me just the push I needed. Thanks again.