parent-child-grandchildren.. (1 Viewer)

[Xx_TownDawg_xX]

I’m thinking about writing a new access project. The application is collecting inspection data, and although I might be better served doing this in excel – I’m willing to look at it as a database concept first.

What I see are essentially three tables: tblPartInfo, tblDataReqs, and tblDataCollection. The description of each would be as follows:

tblPartInfo
The fields include PartNbr (numerical descriptor, example is “221-012”), PartDesc (text descriptor, example is “Housing, Inner), EngRev (A, B, C, etc) , EngDate (dd-mmm-yy) , Supplier (text descriptor, example is “Acme Tool Company”).

Look at this as a heading, and the parent of a parent-child relationship.

tblDataReqs
The fields include Nominal (example is 3.315”), TolUpper (example is 0.010”), TolLower (example is 0.005”), and NomDesc (text descriptor, example is “C/L from edge to mounting hole).

Look at this as a heading, and the child of a parent-child relationship. As with all relationships like this, different parents will have a different amount of children, but all will have at least one, most will have about 5.

tblDataCollection
The fields include Data (example is 3.319”), and Date (dd-mmm-yy)

This is where I think it gets tricky. The way I’d want the application to work would be to select a part (tblPartInfo), which brings up the required measurements (tblDataReqs) to make, and in turn I would collect the actual measurements (tblDataCollection) for each of them.

Creating the form and making it work the way I have envisioned sounds the toughest to me. Obviously for any given part to measure, all the required measurements are made, and evaluated against the tolerances required.

What do you think? Anyone have any experience with an application example that is similar?

 

[The_Doc_Man]

Ah, yes - the devil is always in the details. I agree with you in your assessment that to maintain proper normalization, you have a parent - child - grandchild case.

To do this right, you need to choose useful prime keys (PK). Your Part number field is obviously one PK that makes perfect sense. But what about the individual specification entries? They will also need a PK because of the presence of grandchildren. (The more precise reason is that you need the one side of a one-many relationship to have a PK, and this entry will be the parent of multiple entries differentiated by date.)

You could do this either of two ways, and it makes at least a small difference.

A. Each tolerance has a system-wide unique number. The tolerance table uses the part number as a foreign key (FK) but the tolerance entry also has its own PK.

B. Each tolerance has a per-part-number key that resets for each different part number. The PK for the tolerance table is the compound key of Part number and tolerance number for that part.

OK, now the grandchild table... It has a copy of the PK from the tolerance table (which is either the system-wide unique tolerance entry number or a compound of the part number and the individual part tolerance number) addedt to the date as its compound PK. There is no doubt that the grandchild table WILL have a compound key though the precise nature of that key depends on your choice for the tolerance-spec key.

I would tend to say your get more mileage by choosing a system-wide unique tolerance number. It is easy enough to group them according to part number, would would still be in the table as FK, but not as part of a compound PK. Then your measurements only need the date and tolerance number for their compound PK. You can back-track the part number through the one/many relationship between part number and tolerance spec.

How you do your form depends on how you intend for it to be used. When you make measurements, do you always measure every one every time? I.e. if the part has five tolerance entries, will you ever NOT measure all five tolerances for compliance with spec? Will you ever want to measure just one of the tolerance cases for compliance?

The answer to that question will govern how you would build the form because in one case, you have to have a pick-list. In the other case, you have every entry referenced.

 

[Xx_TownDawg_xX]

But what about the individual specification entries?

I'm thinking of using the partNbr throughout parent-children-grandkids. Let me set up something as an example and see if it works at all..

Update: Oooo.. just realized something... hang on.. it ~is~ a little trickier. I see what you were trying to tell me now.

Any thoughts? I uploaded a dbSample for us to play with.

 

[The_Doc_Man]

Sorry, my site won't allow me to download a database file. Filtration rules and all of that rot associated with my Dept. of Defense site.

Just remember this when trying to normalize: Whatever table you are in, your PK must uniquely and unequivocally lead you to a single record. Whatever child tables you have, each individual FK must uniquely and unequivocally lead you to a single record in the parent table.

If the middle table has its own unique key rather than a compound key, you avoid having to take both the grandparent and parent keys into the child table. As long as the relationships are properly defined and the individual tables that need it have proper PKs, you can do a JOIN-query to recover the parent data having only the FK field.

There is no driving need for the lowest table in the heirarchy to have a PK for linkage - but don't forget lookups, sorting, reporting, and other functions that might require a direct lookup of the lowest-level record for some reason. I.e. working backwards to do a bottom-up product evaluation of some type based on, say, the date fields of your individual measurement table. You could use the Tolerance Specification key and date as drivers for the process OR you could do a quality control sampling. In the latter case, you might wish you had a key on the low-level child in order to drive the sampling table.