I am working as an intern, completing an industry based project with an engineering firm. Successful completion of my project will serve as a possible incentive for employment when I graduate at the years end. I am currently a novice at Access, with some basic knowledge on database design. PLEASE HELP!
The division I am working in performs "level 2" condition inspections on Bridges, where each component (approx. 50 in total, eg. deck, guard rails, abutments etc.) is inspected and assessed in terms of their defects and given a condition rating. Each component has corresponding attribute data (eg. material, geometry, exposure class etc.) An overall condition rating is then assigned to the bridge based on the weighted condition of individual components. I would like the system to have the following basic features:
- Record the component attributes and conditions within the structure.
- Record and score the bridge based on Deterioration attributes of the bridge (eg. environmental/locational data: high traffic volume, high exposure to salt etc.)
- Produce an overall weighted bridge condition score, based on component and environmental/locational conditions.
- Prioritise maintenance works across a number of bridges.
- Ability to store photographs, technical drawings and other electronic documents on each bridge.
I have started creating tables and filling in forms, here's an outline of my database design tables and a few fields:
BridgeOverview:
-BridgeID (autonumber), BridgeName, City, Suburb, AssetOwner etc. I would like this to be a basic overview of all of the bridge inventory.
ComponentModule:
-ComponentID (autonumber), Geometry, Modification, Group, Material etc. This is a detailed table of all components and their attributed. I have a number of drop down lists, but I would like a selection of a later drop-down to be dependant on an initial selection. There will also be a ConditionRating given to each component. Linked BridgeName to this table.
DeteriorationModule:
-DeteriorationID (autonumber), TrafficVolume, Exposure, Climate etc. This is a detailed table of deterioriation. Linked BridgeName to this table.
This is basically all I have to date. I am unaware if my relationships are correct or if I may need further tables for Maintenance prioritisation etc.
What I am wanting to learn is how I may be able to design this properly from the beginning and calculate overall scores of bridges for prioritising maintenance. I have tried to explain clearly what I am trying to achieve. This project is very important to me and any advise at all welcome. Thanks for taking the time to read my thread.