Inspection Tables Picker

[ringoranger]

I’m trying to create an inspection report generated by clicking on multiple drop down boxes. To help me accomplish this, I’ve been doing research online as well as access training. However, I’m still in need of some help in setting up the tables and relationships properly. Here’s some background:
When we receive a component from a customer for inspection, and subsequently repair, I would like our inspector(s) to pick from multiple drop down menus to select recommended repairs.
The hierarchy to select a repair would be as follows:
1. Component
a. Section
i. Feature
1. Findings
a. Recommended Repair(s)
There could be multiple findings per feature each with its own list of recommended repairs. Also the findings and recommended repairs could be the same for different components. For instance, “No relevant findings” and recommended to “Use as is.”
The only information that I need the inspectors to manually input is: Customer name, inspector name, purchase order numbers, and date of inspection. The Recommended Repairs will be pre-populated for the inspectors for every component. Eventually I will need to create a form to revise existing components or add new ones.
Do I need tables for each item with a high likely-hood of repeatability? That is…something similar to the following?
tblComponents – Component ID, Component
tblSections – Section ID, Sections
tblFeatures, Feature ID, Features
tblFindings – Findings ID, Findings
tblRecommended Repairs – Component ID, Section ID, Feature ID, Finding ID, Inspector ID, Customer ID, Recommended Repair
tblInspectors - Inspector ID, First Name, Last Name
tblCustomers – Customer ID, Customer Name, Order ID
tblOrders – Order ID, Purchase order number, Date of Inspection

From what I’ve found online this should be setup as a “survey” type database.

http://en.allexperts.com/q/Using-MS-Access-1440/2011/9/facility-inspection-database.htm

http://elsmar.com/Forums/showthread.php?s=37c1e64562b937e43c54e91c3589457e&t=39108&page=2

 

[lameid]

Without knowing everything involved, having the tables you listed is a good start...

Data Normalization is the process of organizing the data the best way. There are rules aranged as Levels and there are other concepts that have names. If you look at the first 5 levels of Data normaliztion, you will be on your way to understanding how to structure your data.

Having said that, there are practical performance reasons not to normalize some things completely and may be worth considering. The most important thing to understand is if your database is not at least in Third Normal Form, you have done likely done something wrong.

Data Normalization Rules should be easily found with your favorite search engine. There is a Kennel (dogs) example that is very easy to follow (to the extent that it is easy to understand, the 5th rule hurts my head everytime I take a look at it).

One last thought, the first three rules can be summarized as "Everything must relate to the key, the whole key and nothing but the key." (I forget the origin of the quote).

 

[formerTexan]

Without knowing your business model, this is speculative. But rather than maintaining the OrderID in the Customer table, I would put the CustomerID in the Order table. Your current arrangement assumes that each customer will only have one order.

Also leaving the spaces out of field names is good standard procedure. Not all database platforms will tolerate them.

 

[lameid]

Good catch on the Foreign key placement between orders and customers... I clearly read over it.