table relationships and business rules - I'm stuck!

[sansui88]

I am stuck on this problem. The solution may be very simple but I can not see it and I hope someone can help me. I am using MS Access.
I am an elevator mechanic who does service, maintenance and repair work on elevators. I am trying to build a database to help me manage my route. I plan to grow the database over time, recording and managing more and more information.

I have a route ofabout 110 building, or contracts, consisting of about 185 elevator units. Every building has at least one elevator and every building must be associated with a building. So I have:

tblBuildings - ContractNumber PK , BuildingName, SalesPerson, address etc...

tblUnits - ContractNumber FK, UnitNumber PK, StateID, Contract Type, ElevType,etc...



Now, here comes the problem. There are different types of elevators and associated with them there are different kinds of things to keep track of. For example, I have a table called:

tblPressureTests - PressureTestID PK, UnitNumberFK, Test Date, lots of technical data...

This is where I keep record of pressure tests and the associated data. A pressure test is a test requires by the state every three years for hydraulic elevators. A hydraulic elevator is a type of elevator, ElevType. I have tblPressureTests related as a sub-table to tblUnits such that every elevator, whether it is a hydraulic elevator or not, potentially can have a record of a pressure test. There must be a better solution.

There are many attributes, I guess I would call them, of an elevator. Some are common to all but many are dependent on some other attribute of the elevator. Traction elevators, another 'Type' of elevators, never have pressure tests done, but they do have Five Year Full Load Safety Test done, which by the way involves a different set of information to be recorded.

I will take this one step further. All elevators have doors. Passenger elevators have side or center opening doors, either single-speed or two-speed, while freight elevators have biparting doors which can be manual or power operated. These possibilities have nothing to do to with whether it is a hydraulic or traction elevator. This is actually an over simplification of the variations. I am presented with these dilemma over and over as I try to figure out how to organize my fields into table and relate them efficiently.
A pressure test has to be associated with an elevator but not every elevator can have a pressure test associated with it. Only the hydraulic elevators can. So how do I handle this in a database. This must be a very common type of business rule but for some reason I can’t seem to find any examples in my books or any tutorials or examples online.
Any help would be very appreciates.

[orange]

What do you have so far?
Do you have a data model that shows your tables and attributes and relationships between tables?
Do you have a clear statement of your business?

Here is a tutorial that could be helpful to you - you do have to work through it.

[Xipooo]

It sounds like you need to make up an "attributes" table. Give it an ID field, Attribute Name, and a foreign key for the corresponding test that must be run if that attribute is on the elevator. Then make up "details" table for each unit which associates the unit ID with each attribute ID that it has.

From there you can work out what tests need to be run on each unit by looking from the Unit -> Details -> Attributes -> Test

[orange]

I will take this one step further. All elevators have doors. Passenger elevators have side or center opening doors, either single-speed or two-speed, while freight elevators have biparting doors which can be manual or power operated. These possibilities have nothing to do to with whether it is a hydraulic or traction elevator. This is actually an over simplification of the variations.

An Elevator is one of 2 types Passenger/Freight
A Passenger elevator :
-carries people
-has side or center opening doors that may be single or two speed

A Freight elevator :
-carries frieght
-has biparting doors which may be manual or power operated

Be more descriptive in your Names eg what is a unit.

Can a Building exist without a contract?

Work through the tutorial I mentioned in my previous post. Fill out the forms go through all the steps.

Then post back and tell us how you did.