Equipment Inspection Schema

[higleyjp]

Hello there!

First off, I have experience using MS Access, MySQL, and PHP front ends for database design and presentation, but I'm a newbie to FileMaker and am having some trouble picking up on it. I'm contracting for a manufacturing company and setting up an FM database to be used primarily with FM Go on an iPad for on-site equipment inspections. I'll apologize right off the bat for being long-winded, but this project requires quite a bit of exposition.

The company I'm working for has multiple locations, each location has multiple departments, each department has multiple areas, each area has multiple pieces of equipment, and each piece of equipment has multiple functions which need to be inspected. For the purposes of this DB, each location/department/area/equipment/function is unique (there's no need to reference one particular piece of equipment in multiple areas).

I'm afraid I might be overcomplicating things with my schema. I currently have a single thread of related tables for Location, Department, Area, Equipment, and Functions, with the last table storing the many-to-many relationship between equipment and function. I'm wondering if maybe it would be easier to throw some of that normalization out the window and consolidate the information into fewer tables since I'm having trouble getting related records to populate properly. I've tried to use serial numbers for match fields and auto-lookup the value in the lower level table to display the appropriate verbage, but no related records are being found.

Ultimately I would like to set up the Inspection layout so that the location is selected first from a drop-down menu. Once the location is selected a second drop-down should show the departments related to that location. Once the department has been selected I would like to have two portals. The first should show the pieces of equipment for all areas in the selected department, and when a piece of equipment is selected the functions should populate in the second portal. Finally (as if there weren't enough already), when a function is selected it should display a picture or video, if available, of how that function should operate. Inspections are performed at the department level (all areas/equipment/functions in the department will be tested at the same time). Once the review is completed there should be a calculated score for the department based on the number of functions that are working properly out of the total number of functions for that equipment.

Kind of stalled here and I'm just looking for a kick in the right direction. Should I stick with the initial idea to keep everything normalized? Would it be easier to get at certain information if it were stored in the same table (i.e. keep the location/department/area information in the same table as the equipment)?

Thanks in advance for the help.

-J

[comment]

Please elaborate on these two points:

For the purposes of this DB, each location/department/area/equipment/function is unique (there's no need to reference one particular piece of equipment in multiple areas).

... Functions, with the last table storing the many-to-many relationship between equipment and function.

[higleyjp]

1. Although different areas may have similar equipment, each piece of equipment should have it's own record in the Equipment table.

2. The Equipment Function table links the functions to the piece of equipment. Different pieces equipment may have the same functions, for example, Equip1 and Equip2 may both have the Up/Down function, Equip1 has Left/Right which Equip2 does not. I neglected to mention in my initial post that the primary functions are each assigned a Function Code which I'm trying to tie in as well, but some equipment may also have custom functions which do not have a code assigned.

[comment]

Although different areas may have similar equipment, each piece of equipment should have it's own record in the Equipment table.

Of course, but I am still missing a table here, and that is a table of EquipmentTypes. I would think that two pieces of equipment of the same type would also have the same functions that need to be inspected?

If yes, then the next question would be: say a fax machine has an On/Off function, and so does a refrigerator. Do we consider this as one function, assigned to both types - or as two different functions? Obviously, in my example they would be different, but I am not sure what kind of equipment you are dealing with.

[higleyjp]

The equipment being used is proprietary, so I can't go very in-depth as far as the functions they perform, but the Equipment Type is irrelevant as far as the inspection goes. In your example On/Off would be a primary function with an assigned Function Code, and to extend the example, Dial Tone could be a custom function that only applies to the fax machine.

[comment]

Well, then - assuming a function assigned to more than one equipment is exactly the same for all (including picture/video), I think you have something like this:

Note:

1. This is an ERD - the actual implementation will require multiple occurrences of some tables on the relationships graph;

2. I am not entirely convinced you need the EquipmentFunctions join table; you could perhaps get away with a checkbox field in Equipment, and link InspectedItems to both Equipment and Functions.

[higleyjp]

Thanks! I'll take a look at this tonight.