Entity Attribute Value Model datatypes

[Zydeceltico]

Also remember that the Attribute itself can be compound, using two or more fields, potentially reducing the number of unique attributes.

The allowed sub-attributes for the main attributes would be stored in an attribute relationship table.

I am seeing exactly this manifesting. And I see it being helpful. LOTS of tables of metadata - but in the end a well-defined and robust framework - easily update-able.

I can see why some (especially those designing a db with much larger scope than we require) being critical of the required SQL but - for me - and given our more limited scope of both size and number of users - to get the usable data we need - the more complex SQL is an acceptable tradeoff - particularly because some great amount of our foreseeable queries will vary by value criteria only. In other words, I do not foresee a vast array of unique complex queries.

 

[Lightwave]

Hi Z

Here are a few templates that I use for EAVs / child tables

Firstly if I have multiple child records but I want to pull out specific maximums or minimum based on date or vector. When placed in a table this can nicely assist between comparison of entitities.

EG - Please give me a list of last time entities were inspected were inspection dates are held in a child table

Find max and minium child values

Or concatenating records - can be great to get everything onto a single screen for searching both the entity and its children. If a record has lots and lots of children it is less useful tends to work best where you are looking at 1 8 children.
Concatenating records

and of course cross tabulating of the child table extremely but open cross tab results in a table that is just too big in such circumstances limiting the columns makes it much more practical.
Cross tab limit columns

Good luck

 

[Pat Hartman]

Here's an example of an EAV data entry form for an insurance application. This is the ultimate in EAV usage since the User gets to define all the new terms including their data types and what documents they are used in. They can even define a query that can be used to populate a combo. The form looks like all the data entry fields are combos but that is because The Optional Extension Period had the focus. The textbox and combo sit on top of one another and depending on which row has the focus, the textbox or the combo is moved to the top.

I would be happy to demonstrate the app using GoToMeeting if it would help you but i cannot post it.

 

[Pat Hartman]

Here's a picture of the Product Definition form.

 

[Zydeceltico]

I would be happy to demonstrate the app using GoToMeeting if it would help you but i cannot post it.

Pat - I am so grateful for this. Happily - something akin to what you have shared is the only way I can conceive of several pieces of the EAV workflow with an undefined number of inspection/diagnostic criteria.

Just by viewing your jpgs I am validated in thinking that some type of split form is practical. I may being using the incorrect term there but certainly my general inspection event data at the top of the form with the lower half of the form being akin to a datasheet with each new data inspection criteria creating another row.

Then - looking at your Product Definition form I notice that it reminds me very much of what my tblAssemblyComponents table looks like and now what the tblInspectionItems is evolving into. A table conjoining several other data tables creating a sort of metadata table.

And I especially appreciate the instructional text you've added in the right-hand frame of the data entry form: "All fields are required at..........." It may be subtle to some casually reading through this entire post but actually designing a db with this model one (I) am also nagged by the thought that I am not constraining the data collection process enough - if at all. So - - while I do not know what process you have in mind to define the "All fields required...." statement, it does appear obvious to me that you have defined SOME process or there would be no way to determine when a record was complete and could be printed. Given the nature of infinite types and items and the relationships of one to the other my greatest concern has been validation and consistency. The "All fields required..." statement and the very clearly defined Product Definition form give me hope that my inspection items
- while potentially infinite in number - can also be reigned in somehow so they don't get completely out of hand.

I would very much appreciate a GoTo sometime soon at your convenience!

Thank you,

Tim

 

[Zydeceltico]

Hi Z

Here are a few templates that I use for EAVs / child tables

Firstly if I have multiple child records but I want to pull out specific maximums or minimum based on date or vector. When placed in a table this can nicely assist between comparison of entitities.

EG - Please give me a list of last time entities were inspected were inspection dates are held in a child table

Find max and minium child values

Or concatenating records - can be great to get everything onto a single screen for searching both the entity and its children. If a record has lots and lots of children it is less useful tends to work best where you are looking at 1 8 children.
Concatenating records

and of course cross tabulating of the child table extremely but open cross tab results in a table that is just too big in such circumstances limiting the columns makes it much more practical.
Cross tab limit columns

Good luck

Lightwave - Super grateful for these ideas and templates. This is another area of design thought that I have been struggling to get my head around given the EAV model: How do I report such disparate data with various numbers of fields and/or rows? I think my biggest challenge and likely anyone who has ever used the EAV model is visualizing how to aggregate the data into a usable, reportable format given the very robust data collection paradigm.

The more I work to have the DB model reflect the literal workflow in the plant, the more I realize that I have two different requirements: 1) the ability to perform full blown, data field specific inspections and 2) the ability to do spot inspections of a single or not more than two or three criteria. I had been thinking that I could not have both - or that it would be a struggle but I am seeing that differently now. Between the examples that you and Pat have so graciously offered me here I can better understand how I can have spot check inspections right alongside full inspections (and here's the most important part) and still run full detailed reports on number and type of inspection by Job and Date even though the various inspections may have a variety of unrelated data criteria.

The different types of "reporting" examples you've offered will get me far down the road - primarily because just you mentioning that this is how you do it validates me wondering the "how" of it.

I suspect I will be referencing your crosstab query example in particular quite a lot. (Actually - all three will be put to great use.)

Thank You

Tim

 

[Lightwave]

No problem Z

You sound like you are well on your way. It sounds like a fun project.

Those templates are kind of fun as they are nice and theoretically pure so unlikely to really age and should be available in any database (with tweaks to the code and implementation). Generally speaking you can also use these queries as the basis for Forms which is super nice although it should be noted that forms based on more complicated queries are rarely editable. This is a limit of EAV rather than MS Access but it is not difficult to redirect users to an editable master detail form on click event.

Best of luck

 

[Zydeceltico]

Here's an example of an EAV data entry form for an insurance application. This is the ultimate in EAV usage since the User gets to define all the new terms including their data types and what documents they are used in. They can even define a query that can be used to populate a combo.

I would be happy to demonstrate the app using GoToMeeting if it would help you but i cannot post it.

Pat - I am rereading your post again. Are you telling me - and I hope you are - that the user can dynamically, on-the-fly, create a custom data entry form in the child so to speak? Basically, the user selects all the criteria they require at the literal moment they begin data entry? That would be outstanding.

I would also like to know more about the other tabs and 1) how they relate to the Parent; and 2) if they are of the same EAV model (specifically - did you divvy up various predefined attributes across tabs due to function or some other process definition as a means of controlling/limiting/directing workflow).

And the dark green box that appears to be titled "Create Document"........I would like to hear more about that also - both its function and from where it gets its data - or what does the underlying structure look like behind the various choices you offer in the dark green box.

And of course - if it is possible to get a look at the underlying table structure and relationship linkage.

Thank you so much,

Tim

 

[Zydeceltico]

No problem Z

You sound like you are well on your way. It sounds like a fun project.

Those templates are kind of fun as they are nice and theoretically pure so unlikely to really age and should be available in any database (with tweaks to the code and implementation). Generally speaking you can also use these queries as the basis for Forms which is super nice although it should be noted that forms based on more complicated queries are rarely editable. This is a limit of EAV rather than MS Access but it is not difficult to redirect users to an editable master detail form on click event.

Best of luck

In our case the "limitation" you describe when using a complex query as the basis for a form shouldn't be a hindrance. All that is every really required in our reports and forms is a review of how many and what types of inspections were performed FOR A GIVEN JOB. Not much more required detail or calculations than that. In other words, 99.9999999% of the time, the only on-the-fly modifications to one of these complex SQL statements will be choosing a new Job Number which is tiered well above the more intricate JOIN statements required to access the details.

 

[Zydeceltico]

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

 

[Pat Hartman]

Hi,
I was also out of town and then when I got back I had pneumonia. I am well again and would be happy to schedule a GTM to go over the app with you. Evenings work best for me this week. I'm in Connecticut so we're in the same time zone. After 6:30 works this week Wed-Sat. Just send me a PM with a couple of times and your email address and I'll send you an invitation.

As to the required fields. Anything that is always required is in the form header so that validation is done in the form. The other fields are required for certain documents and are validated when the user attempts to print a document.

 

[Pat Hartman]

Wed and Thu evening are gone. Thursday afternoon, fri evening, sat evening are still on the table

 

[Zydeceltico]

Wed and Thu evening are gone. Thursday afternoon, fri evening, sat evening are still on the table

Hi Pat -

I have to go out of town this weekend for a hectic weekend. I'm going to send you a private message.