mpetrie
07-06-2006, 07:36 AM
I'm hoping someone can give some advice on changes to a spares database that I've been trying to find a sensible solution to...
The situation is as follows:
I have a spares database that lists repairable and consumable items.
Some consumable and most repairable items have serial numbers.
Any spare item can be in one or more of three stock rooms - don't ask!
Each repairable spare can have a different warranty end date.
Each major item can also be fitted in one of several slots in one of three flight simulators.
The slot and stock room references use different numbers of identifiers (A320 Ab_J1, B777 Bc_J3, etc for the slots, and A320 Store A1, etc for the stores).
What I'd like to be able to do is:
Track spares quantities.
Track spares stock locations.
Track serial numbers.
Limit items to permitted stock locations.
Limit in service items to permitted locations.
Track items sent for repair.
Allow an item to be in only one place, a permitted place, at a time.
The first bit is easy enough: one table for item part numbers and descriptions, with an additional primary key; one for stock location at the many end of a relation to the part number ID; and another for all the serial numbers again linked to the part number ID.
Is that a sensible enough arrangement? How can I cope with the consumable (untracked) vs. repairable (tracked) items, and how can I cope with the multiple installed and stock locations for each item type? Should I split the parts and different types of location into seperate tables? I've been going round in circles over these questions, but feel that there must be a simple answer that I'm missing.
Mike
The situation is as follows:
I have a spares database that lists repairable and consumable items.
Some consumable and most repairable items have serial numbers.
Any spare item can be in one or more of three stock rooms - don't ask!
Each repairable spare can have a different warranty end date.
Each major item can also be fitted in one of several slots in one of three flight simulators.
The slot and stock room references use different numbers of identifiers (A320 Ab_J1, B777 Bc_J3, etc for the slots, and A320 Store A1, etc for the stores).
What I'd like to be able to do is:
Track spares quantities.
Track spares stock locations.
Track serial numbers.
Limit items to permitted stock locations.
Limit in service items to permitted locations.
Track items sent for repair.
Allow an item to be in only one place, a permitted place, at a time.
The first bit is easy enough: one table for item part numbers and descriptions, with an additional primary key; one for stock location at the many end of a relation to the part number ID; and another for all the serial numbers again linked to the part number ID.
Is that a sensible enough arrangement? How can I cope with the consumable (untracked) vs. repairable (tracked) items, and how can I cope with the multiple installed and stock locations for each item type? Should I split the parts and different types of location into seperate tables? I've been going round in circles over these questions, but feel that there must be a simple answer that I'm missing.
Mike