Hi LW - and everybody - just thought I would touch base as it has been awhile. I was "out of town" for two weeks and then chaos when I got back. Just now returning to this project.
I like EAV models for storing information about entities the attributes of which are not particularly predictable.
.....or in my case........."required." All of our attributes are strictly predictable but very few are always required to be inspected. "Spot check inspections" of a limited and unpredictable amount of attributes are most often what are required. Some problem feature of a product on a large production run or some custom feature the customer requires that is not typical of the base production item that would have a more predictable set of attributes to be inspected.
A few of you have mentioned to me at times that the EAV model requires more front-end work but will operate more efficiently (for my needs) later.
This is super true.
I can see that now. I am reworking my meta-data in Excel pivot tables before I create any tables in Access. It is interesting that, even in this non-traditional model, there still is or at least can be some great degree of normalization between Entities, Attributes, and their relationships in the junction table.
For anyone interested in how I am working through this process, attached to this post is the UNREFINED current worksheet (in process) of my thoughts of how to break down my metadata per Entity. EVentually - once I get it all sorted out, the Attributes column will have dupes removed. Each column will become a field in a junction table. Each column will become a table...and there will be a junction table relating all three per the relationships (in process) shown in my Excel worksheet. DISCLAIMER: I know that I am far from finished with the initial setup. This is the third worksheet/layout that I've started because 1) the EAV model makes sense to me but hurts my brain because "making sense" and understanding are two entirely different things and 2) with each effort of organizing the metadata I see a clearer better way to sort it all.
And as always - - I always benefit from your feedback.
Thanks!
Tim