Do you have a list of "Business facts" that you (and/or the OP and/or ssanfu) have assembled?
Not from OP at this point. Any db posted in the other thread was only remotely related to the original problem, which I think I was able to solve by providing a subquery as a main query field. You got me thinking though.
Let's say the scenario is that the db is primarily a tool for a service provider, not the equipment owner and
- a customer can have 1 unit at 1 location, rented or leased (rl) from the service provider (sp) and maintained by sp
- or the unit may be owned by customer, who is only contracting out its maintenance and repair
- the rl or maintenance may or may not be under contract but likely would be at least under a PO
- the unit may always be at the same location under the same general address
- there may be 1 or more contacts for that customer/unit
- customer owned units might be temporarily replaced by rentals if repairs necessitate going to sp site
OR
- a customer may have many units spread over many of their locations, either at the same plant or not
- all other bulleted points above apply, plus likely that there would be multiple units, locations and contacts
db user should be able to
- return all maintenance history for a unit, regardless of who currently has it
- maintenance history should be relatable to the customer. If a rl situation, I think the customer at that point should be retrievable as well
- modify unit details or add/archive units
- retrieve contact details regarding a particular unit and know its assigned location
Not considering a full fledged application at this point, so going beyond technician ID, PO ID, contract ID (be that autonumber fk or actual values) is probably superfluous since there's no consideration for timesheets, invoicing and many pertinent PO and contract fields. I see that data as links to other systems and would remove them from the tables shown.
That's all I can think of at the moment.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.