Table Design

[Nancy]

I have three different types of aircraft and I need to inventory parts and
assign locations for each aircraft type. I will then combine the three
tables in a query and then develop a search form for the mechanics to search
for the location of a specific part they need to complete a repair. I would
like the mechanic to have the ability to search each field individually or in
combination. The fields that will be searched are as follows:

Nomenclature: (Part Name)
Part Number (would like the search form to search for both primary and
alternate part number at the same time, ie. Put either number into the
search box and have it look for both numbers.
Remarks (Remarks field gives a brief description of the part, ie. Packing
for nose gear.)

At some point, I would like to establish a calculation to automatically
calculate the inventory level for reorder, but that is somewhere down the
road. I just want to make sure the tables are established correctly before I
get too far ahead of myself.

Below is the table structure. Please look at it and see if there is
anything wrong with this approach or if you have any suggestions to make this
a more powerful table.

ID(Aircraft Type ie.CessnaID, WestwindID, CitationID)
Nomenclature
Part Number
Alternate Part Number
Quantity
Location
Remarks
Aircraft Type
Require On-Hand Quantity
Reorder Point.

Thank you

 

[Larry Daugherty]

The respondents here are unpaid volunteers. The general rule of thumb
is one finite technical issue per post. The kind of support that
you've requested is more appropriately made of a consultant on "fee
for service" basis.

In that vein, I'll make just one technical suggestion: You don't need
three tables, one for each type of aircraft. For that one issue,
you'd need one table with a field for "Aircraft Type". The type could
easily be kept in a value list or a lookup table for easy selection.

Everything you've asked can be answered - but one issue at a time.
That way would be much more productive of results as many people might
know things about separate issues who might not respond to a whole
cluster of interrelated issues. Lurkers would learn more from that
process, too.

HTH

 

[Jerry Whittle]

Well as a former aircraft mechanic and current database guy, I see one very
wrong thing with your design: the table per aircraft type. Why?

1. Some parts are used by more than one aircraft.
2. Think of all the trouble that happens when you add a 4th aircraft! You
may need to redesign your forms, reports, and queries. About the time you get
that done, then comes along the 5th aircraft.

Better to have a table of Parts, a table of Aircraft, and a third bridging
table of AircraftParts where you can define which parts are used on what
aircraft AND what aircraft uses which parts.

Also you may need a Location table if the part can be stored in more than
one place.

The Alternate Part Number field could be a problem if there are more than
one alternate parts.

 

[Nancy]

Thanks for both replies. I will try to keep the questions to one finite
technical issue per post.

I see your point about the three tables. I suppose they could be combined
into one table. I guess the reason I was thinking three tables as we
currently store the parts in three different locations based on aircraft
type. I plan to have a seperate table all together with my common hardware
such as nuts and bolts which are used on all the acft. The tables for the
aircraft types were aircraft specific parts and not interchangeable with the
other aircraft. As for the part number, I wanted to be able to capture the
manufactor part number and the alternate (after market) part number. Just a
thought though.

So after reading your thoughts I think I will go with the one table for all
acft with an acft type field. Should I add the location to the table or do I
need a seperate table for location? I am not real strong in Access and if I
do a seperate table I am not sure I competely understand how to relate the
tables in order to find the specific part number locations.

Thanks for all your advice.

 

[Pete D.]

Actually one table with aircraft type will only get you part way there. You
need a many to many relationship to make this work right. Think of it this
way, one part may fit many aircraft and one aircraft may use many parts.
Your table in the middle would use a combination of aircraft type and part
number to link the part table and aircraft table together. For a picture of
this idea see,
http://www.databasedev.co.uk/many_to_many_example.html

 

[Evi]

For your locations, have a table which lists the locations
TblLocation
LocID (primary key)
Location

Where you put the foreign key LocID depends on what is at the location, a
part, an aircaraft or a part-assigned-to-an-aircraft so it could be in
either one of the 3 tables below


TblAircraft
AirID (PrimaryKey)
Aircraft (eg WestWind)


TblParts
PartID(PK)
Part
LocID (if it is the part's location you need to define, rather than the part
when it is put with an aircraft, or the aircraft itself - linked from
TblLocation
RequiredOnHand
Stuff which applies to the part itself and not the part when it is added to
an aircraft

TblPartAircraft
PartAirID (PK)
PartID
AirID


Your actual stock taking will probably require another table eg


There is an Inventory Management template here
http://office.microsoft.com/en-gb/templates/CT101426031033.aspx

which you may be able to adapt or get ideas from.


Evi